node.jspostgresqltimezonedate-arithmetic

Sum time differences across multiple timezones in PostgreSQL

Let's say I have a table with two TIMESTAMPTZ columns - start_time and end_time.

In my JS code I'm going to create start and end points using momentjs, and I want to find all the sum of all time differences (end_time - start_time) for all rows with an end_time between those two points regardless of the timezone on each individual record.

Normally if all the records in the table are using the same timezone (UTC for example) I would just do something like:

SELECT sum(EXTRACT(epoch from (end_time - start_time) / 3600)) as hours_used FROM table

But I'm unsure of how to accomplish this when different time zones are factored in. Main concern is that I don't want to have a few rows left out by accident because they were in a different time zone than my start/end points.

Solution

  • Your query is valid as is, once you append a WHERE clause implementing your desired filter:

    for all records with an end_time between those two points

    SELECT sum(EXTRACT(epoch from (end_time - start_time) / 3600)) as hours_used
FROM   tbl
WHERE  end_time BETWEEN $momentjs_start AND $momentjs_end;

    Where $momentjs_start and $momentjs_end are your created start and end points, which should be timestamptz as well. (For timestamp values, the current time zone of the session is assumed in the assignment cast). BETWEEN includes lower and upper bound, btw.

    Your confusion is probably due to the unfortunate name of the data type timestamp with time zone (= timestamptz), defined by the SQL standard committee. It does not actually store any time zone information. Time zone offset, abbreviation or name in timestamptz literals only serve as input / output modifier to adjust for the time zone. Internally, plain UTC timestamps are stored. So your computation works as is.

    So, this is a given, automatically:

    regardless of the timezone on each individual record.

    Related: