2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
sb9
  • 266
  • 7
  • 22
  • What do you mean `regardless of the timezone`? Such calculation would be pointless. Timezones always need to be accounted for. And for that reason you should do the whole thing inside SQL query, so the timezones are calculated automatically, and do not involve client-side `moment.js` where it is not necessary. – vitaly-t Apr 02 '18 at 18:12
  • So it sounds like this isn't possible then in a single query? Would I have to query the table for sums for each different timezone separately? – sb9 Apr 02 '18 at 19:01
  • You mean start_tz <> to end_tz or (start, end) row tz <> to another row tz ? Anyway. did you try that query? and what result you got? Will be great if you include some sample data and expected result so we can get a better idea. – Juan Carlos Oropeza Apr 02 '18 at 19:09

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thank you for the clarification! Yes, that was certainly a source of confusion for me. – sb9 Apr 04 '18 at 19:58