Skip to content

Handy postgresql templates

Database timezone conversions are really annoying. Some helpful postgresql scripts I used almost daily.

TL;DR

Skip to the timezone templates or how to Import Postgresql Templates into DBeaver

Getting timezwnd

Many moons ago, the company I work for first configured their DB in New Zealand, "Obviously we'll use standard NZ timezone NZST" and the world moved on. More recently after various clients and iot device manufacturers, the question is asked, "Does this device account for daylight savings".

Most experienced software developers will think this is very amateur and of course it is. But the reality is experienced software developers have likely come to this understanding after some long and arduous, critical data migration exercise traumatising enough to KNOW to always always always store time series data in UTC. ALWAYS. Thats a rite of passage we're not always lucky enough to navigate through unscathed.

A classic daylight savings example

Imagine you have a database receiving data from IoT devices capturing electricity data in New Zealand, all accounting for daylight saving time (DST) adjustments. Many smart meters are scattered all over New Zealand, tracking power usage in real-time. They poll data with timestamps corresponding to each datapoint to the millisecond. Whats the issue? Not all devices account for DST.

For daylight savings in NZ: clocks go forward an hour in September, making it NZ Daylight Time (NZDT), and then back an hour to NZ Standard Time (NZST) in April. Now, if some of your meters are smart enough to adjust for this change and others aren't, some data will be shifted by 1 hour 😭.

Skip forward to the first day after daylight savings ends. One meter that doesn't adjust for DST might record electricity usage at 3 AM NZST, while a DST-aware meter records usage at what it thinks is 3 AM but is actually 2 AM NZST. When this data hits your database, it looks like the readings are an hour apart, even though they happened at the same time. This messes up any analysis trying to figure out power consumption patterns or peak usage times.

Here's a simplified version of what your timeseries data might look like:

CREATE TABLE electricity_usage (
    meter_id INT,
    usage_time EPOCH, -- Stores time in epoch format
    kilowatts DECIMAL
);

Let's say you're inserting data for two meters, one DST-aware (Meter A) and one not (Meter B), for the same physical moment when the DST adjustment happens:

-- Meter A adjusts for DST, Meter B does not
INSERT INTO electricity_usage (meter_id, usage_time, kilowatts)
VALUES
    (1, EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-04-07 02:00:00 NZDT' AT TIME ZONE 'UTC'), 5.0), -- DST-aware meter
    (2, EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-04-07 02:00:00 NZST' AT TIME ZONE 'UTC'), 5.0); -- Non-DST-aware meter

Here, both meters are reporting usage for what should be the same time, but because one doesn't adjust for DST, its data will be an hour ahead in your system. This could lead to incorrect assumptions about energy consumption patterns.

The fix? Make sure all your timeseries data is stored in UTC. This way, you avoid the DST dilemma, keeping your data consistent and comparable, no matter where your meters are or how they handle time. Plus, when analyzing the data, you can convert UTC to whatever local time you need, DST adjustments included.

Timezone queries

This timezone template came in very handy. By hitting <TAB> after typing "times" DBeaver pops out the below query.

-- This uses local dbeaver timezone conversion
-- dbeaver assumes you want the unixtime with dbeaver timezone setting
select extract(epoch from now())::int

-- This also uses local machine setting
-- If output from "show timezone" says utc, this will spit out unix in UTC
-- This returns the type of timestamp with timezone i.e. +00:00
select to_timestamp(1682031488)

-- This ignores the local machines settings
-- the "with time zone" command has includes timezone stamp of computer settings 
-- for example with UTC timezone:  2023-04-20 22:58:08.000 +0000
select to_timestamp(1682031488)::timestamp with time zone

-- give epoch timestamp in GMT-12 timezone context
select *, to_timestamp(unixdatetime) at time zone 'Etc/GMT-12'

-- This command uses dbeaver timestamp settings
select to_timestamp(1682031488)::timestamptz

-- This timestamp has no timezone context!
select '2023-04-21 12:00:00'::timestamp

-- "timestamptz" forces the machine setting timezone setting
select '2023-04-21 12:00:00'::timestamptz

-- timestamp shows with session DBeaver timezone
select '2023-04-21 04:00:00'::timestamp with time zone

-- Timestamp may include timezone context
select '2014-08-23 08:50:00.0000 America/New_York'::timestamp

-- Convert timestamp from New York to dbeaver timezone. For example New York to UTC is +4 hrs
select ('2014-08-23 08:50:00.0000 America/New_York'::timestamp with time zone)

-- Convert to new york timezone from current setting to New York. For example UTC to New_York is -4 hrs.
select '2014-08-23 08:50:00.0000'::timestamptz at time zone 'America/New_York'

Import Postgresql Templates into DBeaver

To copy these templates/shortcuts into your DBeaver client:

Uh Oh: Image not found

Here is the xml you will need. I've included a couple more gems I can't go without:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<templates>
    <!-- Existing templates -->
    <template autoinsert="true" context="sql" deleted="false" description="select count with group by" enabled="true" id="org.jkiss.dbeaver.templates.scgb" name="scgb">
        select ${col:column(table)},count(*)
        from ${table} t group by ${col};
    </template>
    <!-- Additional templates -->
    <template autoinsert="true" context="sql" deleted="false" description="Commit Transaction" enabled="true" id="org.jkiss.dbeaver.templates.ct" name="ct">
        --COMMIT TRANSACTION;
    </template>
    <template autoinsert="true" context="sql" deleted="false" description="Rollback and Begin Transaction" enabled="true" id="org.jkiss.dbeaver.templates.rtbt" name="rtbt">
        ROLLBACK TRANSACTION;
        BEGIN TRANSACTION;
    </template>
    <template autoinsert="true" context="sql" deleted="false" description="Create Temp Table" enabled="true" id="org.jkiss.dbeaver.templates.ctemp" name="ctemp">
        create temp table t0 on commit drop as                                     
        with foo (k, v) as (
         values
         (0, 'string'), 
         (1,'description')
        )
        select * from foo;
        select * from t0;
    </template>
    <template autoinsert="true" context="sql" deleted="false" description="Timezone Conversions" enabled="true" id="org.jkiss.dbeaver.templates.times" name="times">
        -- This uses local dbeaver timezone conversion
        -- dbeaver assumes you want the unixtime with dbeaver timezone setting
        select extract(epoch from now())::int
        -- This also uses local machine setting
        -- If output from "show timezone" says utc, this will spit out unix in UTC
        -- This returns the type of timestamp with timezone i.e. +00:00
        select to_timestamp(1682031488)
        -- This ignores the local machines settings
        -- the "with time zone" command has includes timezone stamp of computer settings 
        -- for example with UTC timezone:  2023-04-20 22:58:08.000 +0000
        select to_timestamp(1682031488)::timestamp with time zone
        -- give epoch timestamp in GMT-12 timezone context
        select *, to_timestamp(unixdatetime) at time zone 'Etc/GMT-12'
        -- This command uses dbeaver timestamp settings
        select to_timestamp(1682031488)::timestamptz
        -- This timestamp has no timezone context!
        select '2023-04-21 12:00:00'::timestamp
        -- "timestamptz" forces the machine setting timezone setting
        select '2023-04-21 12:00:00'::timestamptz
        -- timestamp shows with session DBeaver timezone
        select '2023-04-21 04:00:00'::timestamp with time zone
        -- Timestamp may include timezone context
        select '2014-08-23 08:50:00.0000 America/New_York'::timestamp
        -- Convert timestamp from New York to dbeaver timezone. For example New York to UTC is +4 hrs
        select ('2014-08-23 08:50:00.0000 America/New_York'::timestamp with time zone)
        -- Convert to new york timezone from current setting to New York. For example UTC to New_York is -4 hrs.
        select '2014-08-23 08:50:00.0000'::timestamptz at time zone 'America/New_York'
    </template>
    <template autoinsert="true" context="sql" deleted="false" description="Documentation Template" enabled="true" id="org.jkiss.dbeaver.templates.docs" name="docs">
        /*  
         * ###########################################################
         * Docs: <Short desc>
         * 
         * ${cursor}
         * 
         * Date: ${date} Time: ${time}
         * ###########################################################
         */
    </template>
    <template autoinsert="true" context="sql" deleted="false" description="Note Template" enabled="true" id="org.jkiss.dbeaver.templates.note" name="note">
        -- # Note: ${cursor}
    </template>
</templates>

How we migrated huge datasets to UTC

This was neat sincy it dynamically accounted for timezones of each trace.

SET TIME ZONE 'UTC';

SELECT 
    unixdatetime AS existing_unixdatetime, 
    EXTRACT(EPOCH FROM (to_char(to_timestamp(unixdatetime), 'YYYY-MM-DD HH24:MI:SS') || ' ' || trace_timezone)::timestamptz AT time ZONE convertToTimezone) as new_unixdatetime
FROM tracedata
WHERE tracesummaryid = traceId;