Accounting for Dst in Postgres, When Selecting Scheduled Items

Accounting for DST in Postgres, when selecting scheduled items

Use timestamp with time zone (timestamptz) for calculations.

Times for alarms can be time [without time zone].

But you have to save the time zone explicitly for every row.

Never use time with time zone (timetz) It's a logically broken type, its use is discouraged by PostgreSQL. The manual:

The type time with time zone is defined by the SQL standard, but the
definition exhibits properties which lead to questionable usefulness.
In most cases, a combination of date, time, timestamp without timezone, and timestamp with time zone should provide a complete
range of date/time functionality required by any application.

Demo setup:

CREATE TABLE alarm(name text, t time, tz text);
INSERT INTO alarm VALUES
  ('Alfred', '04:00', 'Europe/Stockholm') -- Alfred sets an alarm for 4 AM.
, ('Lotta',  '05:00', 'Europe/Stockholm') -- Lotta sets an alarm for 5 AM. 
, ('Sharon', '11:00', 'Asia/Singapore');  -- Sharon has set an alarm for 11 AM.

It has to be time zone names (not abbreviations) to account for DST. Related:

Time zone names with identical properties yield different result when applied to timestamp

Get matching alarms for "today":

SELECT *
FROM   alarm
WHERE  (('2012-07-01'::date + t) AT TIME ZONE tz AT TIME ZONE 'UTC')::time
       = '03:00'::time

('2012-7-1'::date + t) ... assemble timestamp [without time zone]
Could also just be now()::date + t for "today".
AT WITH TIME ZONE tz ... place timestamp at the saved time zone, resulting in timestamptz.
AT WITH TIME ZONE 'UTC' ... get according UTC timestamp
::time ... simplest way to extract the time component.

Here you can look up time zone names:

SELECT *
FROM   pg_timezone_names
WHERE  name ~~* '%sing%'
LIMIT  10;

db<>fiddle here - demonstrating summer / winter

_{Old sqlfiddle}

Insert time with timezone daylight savings

The proper way is not to use time with time zone (note the space between time and zone) at all, since it is broken by design. It is in the SQL standard, so Postgres supports the type - but advises not to use it. More in this related answer:

Accounting for DST in Postgres, when selecting scheduled items

Since you are having problems with DST, timetz (short name) is a particularly bad choice. It is ill-equipped to deal with DST. It's impossible to tell whether 8:00:00 is in winter or summer time.

Use timestamp with time zone (timstamptz) instead. You can always discard the date part. Simply use start_time::time to get the local time from a timestamptz. Or use AT TIME ZONE to transpose to your time zone.

Generally, to take DST into account automatically, use a time zone name instead of a time zone abbreviation. More explanation in this related question & answer:

Time zone names with identical properties yield different result when applied to timestamp

In your particular case, you could probably use America/Los_Angeles (example with timestamptz):

INSERT INTO mytable(start_time, end_time)
VALUES
   ('1970-01-01 08:00:00 America/Los_Angeles'
  , '1970-01-01 18:00:00 America/Los_Angeles')

I found this by checking:

SELECT * FROM pg_timezone_names 
WHERE  utc_offset = '-07:00'
AND    is_dst;

Basics about time zone handling:

Ignoring time zones altogether in Rails and PostgreSQL

Time zone names with identical properties yield different result when applied to timestamp

Right after I posted this, I ran another query to check on a suspicion:

SELECT * FROM pg_timezone_abbrevs
WHERE  abbrev IN ('CEST', 'CET');

 abbrev | utc_offset | is_dst
--------+------------+--------
 CEST   | 02:00:00   | t
 CET    | 01:00:00   | f

As it turns out, there is also a time zone abbreviation named CET (which makes sense, "CET" being an abbreviation). And it seems that PostgreSQL picks the abbreviation over the full name. So, even though I found CET in the time zone names, the expression '2012-01-18 1:0 CET'::timestamptz is interpreted according to the subtly different rules for time zone abbreviations.

SELECT '2012-01-18 1:0 CEST'::timestamptz(0)
      ,'2012-01-18 1:0 CET'::timestamptz(0)
      ,'2012-01-18 1:0 Europe/Vienna'::timestamptz(0);

      timestamptz       |      timestamptz       |      timestamptz
------------------------+------------------------+------------------------
 2012-01-18 00:00:00+01 | 2012-01-18 01:00:00+01 | 2012-01-18 01:00:00+01


SELECT '2012-08-18 1:0 CEST'::timestamptz(0)
      ,'2012-08-18 1:0 CET'::timestamptz(0)
      ,'2012-08-18 1:0 Europe/Vienna'::timestamptz(0);

      timestamptz       |      timestamptz       |      timestamptz
------------------------+------------------------+------------------------
 2012-08-18 01:00:00+02 | 2012-08-18 02:00:00+02 | 2012-08-18 01:00:00+02

I find 10 cases of time zone abbreviations in the time zone names and fail to understand why those are there. What's the purpose?

Among those, the time offset (utc_offset) disagrees in four cases due to the DST setting:

SELECT n.*, a.*
FROM   pg_timezone_names n 
JOIN   pg_timezone_abbrevs a ON  a.abbrev = n.name
WHERE  n.utc_offset <> a.utc_offset;

 name | abbrev | utc_offset | is_dst | abbrev | utc_offset | is_dst
------+--------+------------+--------+--------+------------+--------
 CET  | CEST   | 02:00:00   | t      | CET    | 01:00:00   | f
 EET  | EEST   | 03:00:00   | t      | EET    | 02:00:00   | f
 MET  | MEST   | 02:00:00   | t      | MET    | 01:00:00   | f
 WET  | WEST   | 01:00:00   | t      | WET    | 00:00:00   | f

In these cases, people may be fooled (like I was), looking up the tz name and finding a time offset that is not actually applied. That is an unfortunate design - if not a bug, at least a documentation bug.

I fail to find anything in the manual about how ambiguities between time zone names and abbreviations are resolved. Obviously abbreviations take precedence.

Appendix B.1. Date/Time Input Interpretation mentions the lookup for time zone abbreviations, but it remains unclear how time zone names are identified and which of them has priority in case of an ambiguous token.

If the token is a text string, match up with possible strings:

Do a binary-search table lookup for the token as a time zone abbreviation.

Well, there is a slight hint in this sentence that abbreviations come first, but nothing definitive. Also, there is a column abbrev in both tables, pg_timezone_namesand pg_timezone_abbrevs ...

Properly handle TIME WITH TIME ZONE in PostgreSQL

You asserted that:

every TIME column represents a moment during the day specified in REPORT_DATE.

So you never cross the a dateline within the same row. I suggest to save 1x date 3x time and the time zone (as text or FK column):

CREATE TABLE legacy_table (
   event_id      bigint PRIMARY KEY NOT NULL
 , report_date   date NOT NULL
 , start_hour    time
 , end_hour      time
 , expected_hour time
 , tz            text  -- time zone
);

Like you already found, timetz (time with time zone) should generally be avoided. It cannot deal with DST rules properly (daylight saving time).

So basically what you already had. Just drop the date component from start_hour, that's dead freight. Cast timestamp to time to cut off the date. Like: (timestamp '2018-03-25 1:00:00')::time

tz can be any string accepted by the AT TIME ZONE construct, but to deal with different time zones reliably, it's best to use time zone names exclusively. Any name you find in the system catalog pg_timezone_names.

To optimize storage, you could collect allowed time zone names in a small lookup table and replace tz text with tz_id int REFERENCES my_tz_table.

Two example rows with and without DST:

INSERT INTO legacy_table VALUES
   (1, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Vienna')  -- sadly, with DST
 , (2, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Moscow'); -- Russians got rid of DST

For representation purposes or calculations you can do things like:

SELECT (report_date + start_hour)    AT TIME ZONE tz AT TIME ZONE 'UTC' AS start_utc
     , (report_date + end_hour)      AT TIME ZONE tz AT TIME ZONE 'UTC' AS end_utc
     , (report_date + expected_hour) AT TIME ZONE tz AT TIME ZONE 'UTC' AS expected_utc
     -- START_HOUR - END_HOUR
     , (report_date + start_hour) AT TIME ZONE tz
     - (report_date + end_hour)   AT TIME ZONE tz AS start_minus_end
FROM   legacy_table;

You might create one or more views to readily display strings as needed. The table is for storing the information you need.

Note the parentheses! Else the operator + would bind before AT TIME ZONE due to operator precedence.

And behold the results:

db<>fiddle here

Since the time is manipulated in Vienna (like any place where silly DST rules apply), you get "surprising" results.

Accounting for DST in Postgres, when selecting scheduled items
Ignoring time zones altogether in Rails and PostgreSQL

Transform timestamp to local time for a given timezone during 'COPY .. TO ..'

First of all, you should use timestamptz instead of timestamp whenever working with multiple times zones. Would avoid the problem completely.

Details:

Ignoring timezones altogether in Rails and PostgreSQL

You can use the AT TIME ZONE construct like @NuLo suggests, it may even work, but not exactly as described.

AT TIME ZONE converts the type timestamp (timestamp without time zone) to timestamptz (timestamp with time zone) and vice versa. The text representation of a timestamptz value depends on the current setting of the time zone in the session in which you run the command. These two timestamptz values are 100 % identical (denote the same point in time):

'2015-09-02 15:55:00+02'::timestamptz
'2015-09-02 14:55:00+01'::timestamptz

But the text representation is not. The display is for different time zones. If you take this string literal and feed it to a timestamp type, the time zone part is just ignored and you end up with different values. Hence, if you run your COPY statement in a session with the same time zone setting as your original timestamp values are for, the suggested operation happens to work.

The clean way, however, is to produce correct timestamp values to begin with by applying AT TIME ZONE twice:

SELECT event AT TIME ZONE 'my_target_tz' AT TIME ZONE 'my_source_tz', ...
FROM   logtable
ORDER  BY event desc;

'my_target_tz' is "your own time zone" and 'my_source_tz' the time zone of the of the cloud server in the example. To make sure that DST is respected use time zone names, not time zone abbreviations. The documentation:

A time zone abbreviation, for example PST. Such a specification merely
defines a particular offset from UTC, in contrast to full time zone names
which can imply a set of daylight savings transition-date rules as well.

Accounting for DST in Postgres, when selecting scheduled items
Time zone names with identical properties yield different result when applied to timestamp

Or, much better yet, use timestamptz everywhere and it works correctly automatically.

Query across timezones

Due to the (rather idiotic, quite frankly) rules for daylight saving times (DST) across the world, a local time can mean all kind of things in absolute (UTC time).

Save a time (not timetz!) and the time zone name (not the abbreviation) for when to send the emails. Tricky details under this related question:

Time zone names with identical properties yield different result when applied to timestamp

CREATE TABLE event (
   event_id serial PRIMARY KEY
 , alarm_time time  -- local alarm time
 , tz text          -- time zone name
 , ...
);

Use the following expression to "cook" the exact daily point in time, taking local DST settings into account:

SELECT current_date + alarm_time AT TIME ZONE tz;

Example:

SELECT current_date + '2:30'::time AT TIME ZONE 'Europe/London' AS alarm_ts

Returns:

alarm_ts
2014-05-19 02:30:00+02

Use timestamp with time zone (timestamptz) across your whole application. Be sure to understand how it works. This comprehensive post may be of help (also explains the AT TIME ZONE construct:

Ignoring timezones altogether in Rails and PostgreSQL

Just to be clear, once you have "cooked" the daily UTC time, you can translate it to and work with any local time just as well. But it might be less confusing to do all the rest in UTC.

Accounting for Dst in Postgres, When Selecting Scheduled Items