Time zone with daylight saving time in PostgreSQL

Question

We are deploying our own flow sensors (which is very similar to this USGS indicator: http://waterdata.usgs.gov/usa/nwis/uv?site_no=03539600 ) so that we, kayaks, know if there is enough water to row a stream and don’t waste time and gas driving there. We hope to establish several of them in the southeastern part of whitewater, which covers the eastern and central time zones.

I save the time when the record is inserted using the default current_time for the record. I would like to display the data later using the format MM/DD/YYYY HH12:MI AM TZ , which outputs as 03/12/2012 01:00 AM CDT . I would also like the output to be noticeable in daylight, so the last part of the previous sentence would change between CST and CDT when we “w760> forward” and “back off”. This change occurred on 11/11/2012 this year, and I have included dates on either side of this DST line below. I use a laptop for Windows 7 for development, and we will later deploy it in a Unix box. Postgres seems to have detected that my Windows computer is installed in the eastern US time zone. I am trying to do this with the "timestamp without time zone" field and the "timestamp with time zone" field, but cannot make it work.

I tried to use "in time zone" in my settings, and everything works until it shows the time zone. The actual hour is part of the timestamp correctly deducted by the hour when I ask for time in the CDT. But EDT is displayed on output.

 SELECT reading_time as raw, reading_time at time zone 'CDT', to_char(reading_time at time zone 'CDT', 'MM/DD/YYYY HH12:MI AM TZ') as formatted_time FROM readings2; "2012-04-29 17:59:35.65";"2012-04-29 18:59:35.65-04";"04/29/2012 06:59 PM EDT" "2012-04-29 17:59:40.19";"2012-04-29 18:59:40.19-04";"04/29/2012 06:59 PM EDT" "2012-03-10 00:00:00";"2012-03-10 00:00:00-05";"03/10/2012 12:00 AM EST" "2012-03-11 00:00:00";"2012-03-11 00:00:00-05";"03/11/2012 12:00 AM EST" "2012-03-12 00:00:00";"2012-03-12 01:00:00-04";"03/12/2012 01:00 AM EDT" 

I save the time zone so that each of our sensors is in the field with a variable symbol separately. I thought I was just adding this value to the end of the time output, but I want it to change from CST to CDT without my intervention.

Thanks for your help.

Answer 1

Instead of using time zone names such as CDT or CST, you can use Olsen-style full time zone names . In the case of central time, you can select the time zone. Either the one that matches your location, for example America/Chicago , or just US/Central . This ensures that PostgreSQL uses the Olsen tz database to automatically determine if daylight saving time is applied at any given date.

Answer 2

You definitely need a TIMESTAMP WITH TIME ZONE column (which is also known as timestamptz in PostgreSQL). This will store the timestamp in UTC so that it represents a specific point in time. Contrary to what the name suggests, it does not save the time zone in the column - you can view the received time stamp in the time zone of your choice using the phrase AT TIME ZONE .

The semantics of TIMESTAMP WITHOUT A TEMPORARY ZONE are confused and almost useless. I highly recommend that you not use this type at all for what you are describing.

I am really confused by the part of the question that talks about saving the timestamp in the CHARACTER VARYING column. It seems like this could be part of the problem. If you can save it to timestamptz from the very beginning, I suspect you will have fewer problems. If this is excluded, it would be safer to use the -04 notation to offset from UTC; but it seems to me that I no longer need any benefit.

Answer 3

You can create a table of known time zones in the format suggested in Guan Yang answer , and then use the foreign key column in this table. Valid time intervals can be obtained from pg_timezone_names I have examined this related answer in detail.