PostgreSQL date() with timezone

Question

I'm having an issue selecting dates properly from Postgres - they are being stored in UTC, but not converting with the Date() function properly.

Converting the timestamp to a date gives me the wrong date if it's past 4pm PST.

2012-06-21 should be 2012-06-20 in this case.

The starts_at column datatype is timestamp without time zone. Here are my queries:

Without converting to PST timezone:

Select starts_at from schedules where id = 40;

      starts_at      
---------------------
 2012-06-21 01:00:00

Converting gives this:

Select (starts_at at time zone 'pst') from schedules where id = 40;
        timezone        
------------------------
 2012-06-21 02:00:00-07

But neither convert to the correct date in the timezone.

Answer 1

Basically what you want is:

$ select starts_at AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific' from schedules where id = 40

I got the solution from this article is below, which is straight GOLD!!! It explains this non-trivial issue very clearly, give it a read if you wish to understand pstgrsql TZ management better.

Expressing PostgreSQL timestamps without zones in local time

Here is what is going on. First you should know that 'PST timezone is 8 hours behind UTC timezone so for instance Jan 1st 2014, 4:30 PM PST (Wed, 01 Jan 2014 16:00:30 -0800) is equivalent to Jan 2nd 2014, 00:30 AM UTC (Thu, 02 Jan 2014 00:00:30 +0000). Any time after 4:00pm in PST slips over to the next day, interpreted as UTC.

Also, as Erwin Brandstetter mentioned above, postresql has two type of timestamps data type, one with a timezone and one without. If your timestamps include a timezone, then a simple:

$ select starts_at AT TIME ZONE 'US/Pacific' from schedules where id = 40

will work. However if your timestamp is timezoneless, executing the above command will not work, and you must FIRST convert your timezoneless timestamp to a timestamp with a timezone, namely a UTC timezone, and ONLY THEN convert it to your desired 'PST' or 'US/Pacific' (which are the same up to some daylight saving time issues. I think you should be fine with either).

Let me demonstrate with an example where I create a timezoneless timestamp. Let's assume for convenience that our local timezone is indeed 'PST' (if it weren't then it gets a tiny bit more complicated which is unnecessary for the purpose of this explanation).

Say I have:

$ select timestamp '2014-01-2 00:30:00' AS a, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AS b,  timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AT TIME ZONE 'PST' AS c, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

This will yield:

"a"=>"2014-01-02 00:30:00"   (This is the timezoneless timestamp)
"b"=>"2014-01-02 00:30:00+00" (This is the UTC TZ timestamp, note that up to a timezone, it is equivalent to the timezoneless one)
"c"=>"2014-01-01 16:30:00" (This is the correct 'PST' TZ conversion of the UTC timezone, if you read the documentation postgresql will not print the actual TZ for this conversion)
"d"=>"2014-01-02 08:30:00+00"

The last timestamp is the reason for all the confusion regarding converting timezoneless timestamp from UTC to 'PST' in postgresql. When we write:

timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

We are taking a timezoneless timestamp and try to convert it to 'PST TZ (we indirectly assume that postgresql will understand that we want it to convert the timestamp from a UTC TZ, but postresql has plans of its own!). In practice, what postgresql does is it takes the timezoneless timestamp ('2014-01-2 00:30:00) and treats it as if it WERE ALREADY a 'PST' TZ timestamp (i.e: 2014-01-2 00:30:00 -0800) and converts that to UTC timezone!!! So it actually pushes it 8 hours ahead instead of back! Thus we get (2014-01-02 08:30:00+00).

Anyway, this last (un-intuitive) behavior is the cause of all confusion. Read the article if you want a more thorough explanation, I actually got results which are a bit different then their on this last part, but the general idea is the same.