0

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sehael
  • 3,678
  • 21
  • 35
  • Hi check this answer already someone posted question about it http://stackoverflow.com/a/11148548/2478386 i hope that will help you. – Anshul Sharma Oct 29 '13 at 04:34
  • While you can store *time with time zone* omitting the date, this is a very bad plan. See documentation for [TIME ZONES](https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-TIMEZONES); "We do *not' recommend using the type time with time zone." – Belayer Jun 17 '21 at 18:59

2 Answers2

5

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:

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • How about time **without** time zone? – Kazuya Gosho Jun 17 '21 at 07:22
  • 1
    @KazuyaGosho: If you are really only interested in the time of day (as shown by a clock on the wall) and nothing else, then `time` is just fine. Well, there are still the corner case ambiguities introduced by disrupting the flow of time with silly DST rules (DST will be removed by every halfway sane government, soon.) – Erwin Brandstetter Jun 17 '21 at 13:37
  • Thanks for your reply! I'm recently working on this kind of stuff and went into this problem. Saving time value looks so simple, but things become complicated when querying and converting into multiple time zone. I'll follow your answer and use timestamptz and discard date part. Thanks!! – Kazuya Gosho Jun 18 '21 at 16:17
0

How about this?

INSERT INTO mytable(start_time, end_time)
VALUES('08:00:00'::time at time zone 'MST7MDT', '18:00:00'::time at time zone 'MST7MDT')
Kazuya Gosho
  • 996
  • 13
  • 14