Forum OpenACS Q&A: to_char(now(), 'YYYY') and time zones

I'm trying to pull the current year with the following query:

select to_char(now(), 'YYYY');

This is fine. Now, I  would like to select this date in different time
zones.

Normally I just do:

select now() at time zone 'utc';

I guess I need a combination of the 2 queries above - one that asks
for ONLY the year in a certain time zone (fx UTC or CET).

Best regards,

Collapse
Posted by Don Baccus on
PG 7.1 now supports subselects in from clauses, so

select to_char(utc, 'YYYYY') from (select now() as utc at time zone 'utc')

Should work, maybe?  You might need to give a name to the subselect and fully qualify the argument to to_char, I didn't try it...

Collapse
Posted by Simon Carstensen on
Don --

Thanks for the response - it seems to me that it might be the solution. But it isn't working.

I get:

ERROR: parser: parse error at or near "at"

I don't know how to give the subselect a name, nor how to fully qualify the argument to to_char (don't even know what that means)...Can you help me?

Yours,

Collapse
Posted by Don Baccus on
Ugh...maybe the "at" clause follows the entire query and therefore can't be buried in a subquery...

I have no experience playing with timezone issues in PG, I'm afraid.  I suggest you go ask the PG folks directly.  If they figure out a way to do this in a single query, let us know, OK?

Collapse
Posted by Dan Wickstrom on
Try this:
openacs4=# select to_char((select now() at time zone 'utc')::timestamp,'YYYY');  
to_char 
---------  
2001 
(1 row)                                                                                                                      
Collapse
Posted by Simon Carstensen on
That did the trick - thanks Dan.
Collapse
Posted by Simon Carstensen on
It doesn't seem to be working after all:

=#select to_char((select now() at time zone 'utc')::timestamp,'HH24:MI');
to_char 
---------  
03:43
(1 row)

=#select now() at time zone 'utc';
        timezone
------------------------
2001-04-12 10:43:51+00
(if it was working, the time should be the same)
Collapse
Posted by Dan Wickstrom on
As an intermediate step, it's converting the time back to your local time zone. Try this instead:

openacs4=# select to_char(now(),'yyyy-mm-dd hh24:mi') at time zone 'utc';
        timezone
------------------------
 2001-04-12 11:48:00+00
(1 row)
 
openacs4=# select now() at time zone 'utc';
        timezone
------------------------
 2001-04-12 11:48:12+00
(1 row)
     

 openacs4=# select now();
          now
------------------------
 2001-04-12 07:50:26-04
(1 row)
                                                                                                                                                                                                                                                    
Collapse
Posted by Simon Carstensen on
So how do I only pull the time? I actually only need the "11:48" part, but that doesn't seem to be working with your query. Or just the year?
Collapse
Posted by Dan Wickstrom on
If you need the time, you could just get the substring that corresponds to the time:

openacs4=# select substr(now() at time zone 'utc',11,6);
 substr
--------
  12:19
(1 row)
 
openacs4=# select now() at time zone 'utc';
        timezone
------------------------
 2001-04-12 12:19:58+00
(1 row)                                                                                                                      
Collapse
Posted by Simon Carstensen on
Great, that seems to be working. But what I'm not sure I understand the query - where does 11 and 6 come in? It's necessary for me to now how this works or else I cannot use it to return the year and the month...
Collapse
Posted by Dan Wickstrom on
pg is implicitly casting the timestamp to a string, so 11 and is the offset for the time portion of the string and 6 is the length. Actually, I made a mistake the offset should have been 12 and the length should have been five. So, if you wanted the year and the month, you would do:

openacs4=# select substr(now() at time zone 'utc',1,4);
 substr
--------
 2001
(1 row)
 
openacs4=# select substr(now() at time zone 'utc',6,2);
 substr
--------
 04
(1 row)                                                                                                                      
Collapse
Posted by Simon Carstensen on
Thanks Dan, I think I get the idea now.

Now that we're at it, can I ask you one more thing? Say I've selected the current month in the time zone 'utc', now, is there a way I can select the next month or the month before. So if I fx selected "12" (using your query) is it then possible to make Postgres compute the next month - that would be 01. Or the next year if it's the year I've queried for, or the day?

Collapse
Posted by Dan Wickstrom on
Use relative times:

openacs4=# select to_char(substr(now() at time zone 'utc',1,19)::timestamp + '1 month'::interval,'MM');
 to_char 
---------
 05
(1 row)

openacs4=# select to_char(substr(now() at time zone 'utc',1,19)::timestamp + '1 year'::interval,'YYYY');
 to_char 
---------
 2002
(1 row)