title: Time zone with daylight savings times in PostgreSQL
body: <p>We're deploying our own stream gauges (a lot like this USGS gauge: <a href="http://waterdata.usgs.gov/usa/nwis/uv?site_no=03539600" rel="noreferrer">http://waterdata.usgs.gov/usa/nwis/uv?site_no=03539600</a>) so us kayakers know whether or not there's enough water to paddle the stream and don't waste time and gas to drive out there.  We hope install a few of these across the southeast whitewater region which spans the eastern and central time zones.  </p>
  
  <p>I'm storing the time a record is inserted using the default value of current_time for the record.  I'd like to later display the data using the <code>MM/DD/YYYY HH12:MI AM TZ</code> format, which outputs reading like <code>03/12/2012 01:00 AM CDT</code>. I'd also like for the output to be aware of changes in day light savings time, so the last part of the previous sentence would change between CST and CDT when we 'spring forward' and 'fall back'.  This change occurred on 3/11/2012 this year and I've included dates on both sides of this DST line below.  I'm using my Windows 7 laptop for development and we will later be deploying on a Unix box.  Postgres has apparently detected that my Windows computer is set to eastern US time zone. I'm trying this with a 'timestamp without time zone' field and a 'timestamp with time zone' field but  can't get it to work.  </p>
  
  <p>I've tried using 'at time zone' in my selects and every thing is working until it's time to display the time zone.  The actual hour is part of the time stamp is correctly subtracted by an hour when I ask for the time in CDT.  But EDT is displayed in the output.</p>
  
  <pre class="lang-sql prettyprint-override"><code>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"
  </code></pre>
  
  <p>I'm storing the time zone that each of our gauges is located in a character varying field a separate table.  I considered just appending this value to the end of the time output, but I want it to change from from CST to CDT without my intervention.</p>
  
  <p>Thanks for your help.</p>