Edit Question - KnowledgeHub

Title Body

We're deploying our own stream gauges (a lot like this USGS gauge: http://waterdata.usgs.gov/usa/nwis/uv?site_no=03539600 ) 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. 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 MM/DD/YYYY HH12:MI AM TZ format, which outputs reading like 03/12/2012 01:00 AM CDT . 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. 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. 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:5

Tags (comma-separated) Cancel