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 ↩︎