Thread: Time is off in PG server

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?

--
Warmest regards,
Ericson Smith
Tracking Specialist/DBA
+-----------------------+------------------------------+
| http://www.did-it.com | Need help tracking your paid |
| eric@did-it.com       | search campaigns?            |
| 516-255-0500          |        - Help is on the way! |
+-----------------------+------------------------------+

On Thu, Aug 26, 2004 at 09:47:26AM -0400, Ericson Smith scratched on the wall:
> 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?

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

   -j

--
                     Jay A. Kreibich | Integration & Software Eng.
                        jak@uiuc.edu | Campus IT & Edu. Svcs.
          <http://www.uiuc.edu/~jak> | University of Illinois at U/C

Ericson Smith <eric@did-it.com> writes:
> 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

Tom Lane wrote:

>Ericson Smith <eric@did-it.com> writes:
>
>
>>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
instead of pm). However, I tried:

 > set local time zone 'EST5EDT';
SET
 > select now();
 now
-------------------------------
 2004-08-26 10:17:45.472901-05

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

- Ericson

Ericson Smith <eric@did-it.com> writes:
> I realized I made a mistake in that initial email (should have said 12am
> instead of pm). However, I tried:

>>> set local time zone 'EST5EDT';
> SET
>>> 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

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

>"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
>
>