Insert time with timezone daylight savings

问题:

I would like to insert time data type in postgresql that includes the timezone and is aware of daylight savings time. This is what I have done:

CREATE TABLE mytable(
    ...
    start_time time(0) with time zone,
    end_time time(0) with time zone
)

INSERT INTO mytable(start_time, end_time)
VALUES(TIME '08:00:00 MST7MDT', TIME '18:00:00 MST7MDT')

I get the following error:

invalid input syntax for type time: "08:00:00 MST7MDT"

It works if I use 'MST' instead of 'MST7MDT', but I need it to be aware of DST. I also tried using 'America/Edmonton' as the timezone, but I got the same error.

What is the proper way to insert a time value (not timestamp) with timezone and DST?

EDIT: I would actually like to use the 'America/Edmonton' syntax

回答1:

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 timezones altogether in Rails and PostgreSQL