Обсуждение: 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).

I guess I should do some sort of subselect like:

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

But that jsut gives me an error.

Does anyone know how to do this?


-- Simon Carstensen

dev=# select to_char((now() at time zone 'utc')::date, 'YYYY');
 to_char
---------
 2001
(1 row)

>> -----Original Message-----
>> From: Simon Bæk Carstensen [mailto:simonbc@email.com]
>> Sent: 12 April 2001 12:40
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] 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).
>>
>> I guess I should do some sort of subselect like:
>>
>> select to_char(utc, 'YYYYY') from (select now() as utc at time zone
>> 'utc')
>>
>> But that jsut gives me an error.
>>
>> Does anyone know how to do this?
>>
>>
>> -- Simon Carstensen
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to
>> majordomo@postgresql.org)
>>

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________


Simon Bæk Carstensen wrote:

> 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).

The easiest way is to do

SET TimeZone TO 'CET';

[from now on all timestamps will be returned CET-base]

SELECT ...

You can then

SET TimeZone TO DEFAULT;

or wait for the end of the connection.

--
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://village.albourne.com
Nicosia, Cyprus             phone: +357-2-755750

"It is more complicated than you think"
        -- The Eighth Networking Truth from RFC 1925