Duckdb Timestamps

timestamps.sql

INSTALL 'icu';
LOAD 'icu';

SET TimeZone = 'Europe/Berlin';


create or replace table metrics as SELECT TIMESTAMP '2024-06-07 00:00:00' AS mytimestamp, TIMESTAMP WITH TIME ZONE '2024-06-07 00:00:00+02' AS mytimestamptz, 1000 AS sales, 50 AS orders, 20 AS customers;

-- Check the stored timestamps to ensure they match Berlin time
SELECT 
    mytimestamp,
    mytimestamp AT TIME ZONE 'UTC' AS utc_time,
    mytimestamp AT TIME ZONE 'Europe/Berlin' AS berlin_time,
    mytimestamptz,
    mytimestamptz AT TIME ZONE 'UTC' AS utc_time_tz,
    mytimestamptz AT TIME ZONE 'Europe/Berlin' AS berlin_time_tz
FROM metrics;

-- ┌─────────────────────┬──────────────────────────┬──────────────────────────┬──────────────────────────┬─────────────────────┬─────────────────────┐
-- │     mytimestamp     │         utc_time         │       berlin_time        │      mytimestamptz       │     utc_time_tz     │   berlin_time_tz    │
-- │      timestamp      │ timestamp with time zone │ timestamp with time zone │ timestamp with time zone │      timestamp      │      timestamp      │
-- ├─────────────────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼─────────────────────┼─────────────────────┤
-- │ 2024-06-07 00:00:00 │ 2024-06-07 02:00:00+02   │ 2024-06-07 00:00:00+02   │ 2024-06-07 00:00:00+02   │ 2024-06-06 22:00:00 │ 2024-06-07 00:00:00 │
-- └─────────────────────┴──────────────────────────┴──────────────────────────┴──────────────────────────┴──────────────────s───┴─────────────────────┘
--   we assume berlin?   well this says 2am berlin =/=      midnight berlin    | midnight berlin          = 10pm utc            =   midnight berlin                


-- Change the timezone to UTC
SET TimeZone = 'UTC';

-- Check the stored timestamps to ensure they match UTC time
SELECT 
    timestamp,
    timestamp AT TIME ZONE 'UTC' AS utc_time,
    timestamp AT TIME ZONE 'Europe/Berlin' AS berlin_time,
    timestamptz,
    timestamptz AT TIME ZONE 'UTC' AS utc_time_tz,
    timestamptz AT TIME ZONE 'Europe/Berlin' AS berlin_time_tz
FROM metrics;

-- ┌─────────────────────┬──────────────────────────┬──────────────────────────┬──────────────────────────┬─────────────────────┬─────────────────────┐
-- │     mytimestamp     │         utc_time         │       berlin_time        │      mytimestamptz       │     utc_time_tz     │   berlin_time_tz    │
-- │      timestamp      │ timestamp with time zone │ timestamp with time zone │ timestamp with time zone │      timestamp      │      timestamp      │
-- ├─────────────────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼─────────────────────┼─────────────────────┤
-- │ 2024-06-07 00:00:00 │ 2024-06-07 00:00:00+00   │ 2024-06-06 22:00:00+00   │ 2024-06-06 22:00:00+00   │ 2024-06-06 22:00:00 │ 2024-06-07 00:00:00 │
-- └─────────────────────┴──────────────────────────┴──────────────────────────┴──────────────────────────┴─────────────────────┴─────────────────────┘
--  we assume utc midnight  this says midnight utc =/=  and this says 10pm utc |     this says 10pm utc   =     10pm utc        =   midnight berlin