Menu
I am trying to extract the date from a query in postgres. The timestamp is stored as UTC, so if I have 1/1/2014 02:00:00, I want the date in pacific time, to be 12/31/2013, not 1/1/2014. I am really close, but both query 2 and 3 still return 1/1/2014.
SELECT '1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles'; returns
2014-01-01 02:00:00-08 -
SELECT CAST('1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles' AS Date); returns
2014-01-01 but I want it to return 2013-12-31.
SELECT CAST('1-1-2014 00:02:00'::timestamp at time zone 'America/Los_Angeles' AS Date) at time zone 'America/Los_Angeles'; returns
2014-01-01 00:00:00 but I want it to return 2013-12-31 00:00:00
I basically want to return the date in the timezone it is in, in this case the pacific timezone.
762
That is given a timezone I can get the timezone offset by the following code snippet: TimeZone tz = TimeZone. getDefault(); System. out.
Knowing just the offset from UTC, you can't tell what timezone you are in, because of DST. You could consider looking at the time part of the time to try to guess whether DST was in effect then or not, but political considerations make that nearly impossible, as different jurisdictions change the definition of DST.
The zone offset can be Z for UTC or it can be a value "+" or "-" from UTC. For example, the value 08:00-08:00 represents 8:00 AM in a time zone 8 hours behind UTC, which is the equivalent of 16:00Z (8:00 plus eight hours). The value 08:00+08:00 represents the opposite increment, or midnight (08:00 minus eight hours).
If it is timestamp without time zone you need first to tell it to use the UTC time zone and then convert to another time zone:
SELECT '1-1-2014 02:00:00'::timestamp at time zone 'UTC' at time zone 'America/Los_Angeles'; timezone --------------------- 2013-12-31 18:00:00 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
