Time is off in PG server

[Ericson Smith]

Hi,

When using date oriented functions on Postgresql, the time is an hour
off, or in certain times, one hour ahead.

System Timezone: EST
System Time (date command): Thu Aug 26 09:44:28 EDT 2004
SELECT now(); : 2004-08-26 08:44:31.307343-05
SELECT date_part('epoch', '2004-08-26'::timestamp) ; : 1093496400 (1am
on that day -- should be 12pm)

Any suggestions?

[Jay A. Kreibich]

Post by Ericson Smith
Hi,
When using date oriented functions on Postgresql, the time is an hour
off, or in certain times, one hour ahead.
System Timezone: EST

^^^

Post by Ericson Smith
System Time (date command): Thu Aug 26 09:44:28 EDT 2004

^^^

Post by Ericson Smith
SELECT now(); : 2004-08-26 08:44:31.307343-05
SELECT date_part('epoch', '2004-08-26'::timestamp) ; : 1093496400 (1am
on that day -- should be 12pm)
Any suggestions?

Work in the same timezone. EST and EDT are not the same.

-j

[Tom Lane]

Post by Ericson Smith
When using date oriented functions on Postgresql, the time is an hour
off, or in certain times, one hour ahead.
System Timezone: EST
System Time (date command): Thu Aug 26 09:44:28 EDT 2004
SELECT now(); : 2004-08-26 08:44:31.307343-05
SELECT date_part('epoch', '2004-08-26'::timestamp) ; : 1093496400 (1am
on that day -- should be 12pm)

Looks exactly right to me. 1093496400 corresponds to 1AM EDT, or
midnight EST, and after all you do have the timezone set to EST.
Possibly you want the zone set to EST5EDT instead.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ericson Smith]

Post by Tom Lane

Post by Ericson Smith
When using date oriented functions on Postgresql, the time is an hour
off, or in certain times, one hour ahead.
System Timezone: EST
System Time (date command): Thu Aug 26 09:44:28 EDT 2004
SELECT now(); : 2004-08-26 08:44:31.307343-05
SELECT date_part('epoch', '2004-08-26'::timestamp) ; : 1093496400 (1am
on that day -- should be 12pm)

Looks exactly right to me. 1093496400 corresponds to 1AM EDT, or
midnight EST, and after all you do have the timezone set to EST.
Possibly you want the zone set to EST5EDT instead.
regards, tom lane

I realized I made a mistake in that initial email (should have said 12am

Post by Tom Lane
set local time zone 'EST5EDT';

SET

Post by Tom Lane
select now();

now
-------------------------------
2004-08-26 10:17:45.472901-05

[***@pg data]# date
Thu Aug 26 11:21:01 EDT 2004

- Ericson

[Tom Lane]

Post by Ericson Smith
I realized I made a mistake in that initial email (should have said 12am

Post by Tom Lane
set local time zone 'EST5EDT';

SET

Post by Tom Lane
select now();

"set local" was probably not what you wanted to use here. Per the man page:

Note that SET LOCAL will appear to have no effect if it is executed outside
a BEGIN block, since the transaction will end immediately.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ericson Smith]

Making the setting in the postgresql.conf file worked after i HUP'd the
postmaster, and logged back into my psql sessions.

timezone = 'EST5EDT'

Thanks a million.
- Ericson

Post by Tom Lane
Note that SET LOCAL will appear to have no effect if it is executed outside
a BEGIN block, since the transaction will end immediately.
regards, tom lane