/ Bit by bit / posts

SQLite: show UTC timestamp as local time

Jun 24, 2019

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:

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.

  1. Okay, to be fair, SQLite employs what it terms as type affinity ↩︎