A business unit asked me to compile some trading volume stats by trading hour. The good thing is that I have the following in SQLite database; the bad thing is the database stores the timestamp as follows:
timestamp
column is stored as a string1, so rather than doing timezone
arithmetic, I used SQLite’s built-in date/time functions instead:
WITH
epochized AS (
SELECT STRFTIME('%s', [timestamp]) AS [epoch] -- convert to unix epoch
FROM trades),
localized AS (
SELECT TIME([epoch], 'unixepoch', 'localtime') -- convert epoch to local time
FROM dropped_offsets),
SELECT SUBSTR([localtime], 1, 2) AS [trading_hour] -- extract hour
FROM localized;
The above employs common table expressions for clarity; the actual combined them as one instead.
-
Okay, to be fair, SQLite employs what it terms as type affinity ↩︎