pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Time is off in PG server
6+ messages / 3 participants
[nested] [flat]

* Time is off in PG server
@ 2004-08-26 13:47 Ericson Smith <eric@did-it.com>
  2004-08-26 14:38 ` Re: Time is off in PG server Jay A. Kreibich <jak@uiuc.edu>
  2004-08-26 15:05 ` Re: Time is off in PG server Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 6+ messages in thread

From: Ericson Smith @ 2004-08-26 13:47 UTC (permalink / raw)
  To: pgsql-admin

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! |
+-----------------------+------------------------------+ 

Attachments:

eric.vcf (text/x-vcard, 313B) download begin:vcard fn:Ericson Smith n:Smith;Ericson org:Did-it.com;Programming adr:#304;;55 Maple Avenue;Rockville Center;NY;11570;USA email;internet:eric@did-it.com title:Web Developer tel;work:516-255-0500 tel;cell:646-483-3420 note:Nothing special! x-mozilla-html:FALSE url:http://www.did-it.com version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: Time is off in PG server
  2004-08-26 13:47 Time is off in PG server Ericson Smith <eric@did-it.com>
@ 2004-08-26 14:38 ` Jay A. Kreibich <jak@uiuc.edu>
  1 sibling, 0 replies; 6+ messages in thread

From: Jay A. Kreibich @ 2004-08-26 14:38 UTC (permalink / raw)
  To: Ericson Smith <eric@did-it.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Time is off in PG server
  2004-08-26 13:47 Time is off in PG server Ericson Smith <eric@did-it.com>
@ 2004-08-26 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-26 15:22   ` Re: Time is off in PG server Ericson Smith <eric@did-it.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-08-26 15:05 UTC (permalink / raw)
  To: Ericson Smith <eric@did-it.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Time is off in PG server
  2004-08-26 13:47 Time is off in PG server Ericson Smith <eric@did-it.com>
  2004-08-26 15:05 ` Re: Time is off in PG server Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-26 15:22   ` Ericson Smith <eric@did-it.com>
  2004-08-26 15:31     ` Re: Time is off in PG server Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Ericson Smith @ 2004-08-26 15:22 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

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

Attachments:

eric.vcf (text/x-vcard, 313B) download begin:vcard fn:Ericson Smith n:Smith;Ericson org:Did-it.com;Programming adr:#304;;55 Maple Avenue;Rockville Center;NY;11570;USA email;internet:eric@did-it.com title:Web Developer tel;work:516-255-0500 tel;cell:646-483-3420 note:Nothing special! x-mozilla-html:FALSE url:http://www.did-it.com version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: Time is off in PG server
  2004-08-26 13:47 Time is off in PG server Ericson Smith <eric@did-it.com>
  2004-08-26 15:05 ` Re: Time is off in PG server Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-26 15:22   ` Re: Time is off in PG server Ericson Smith <eric@did-it.com>
@ 2004-08-26 15:31     ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-26 16:14       ` Re: Time is off in PG server Ericson Smith <eric@did-it.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-08-26 15:31 UTC (permalink / raw)
  To: Ericson Smith <eric@did-it.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Time is off in PG server
  2004-08-26 13:47 Time is off in PG server Ericson Smith <eric@did-it.com>
  2004-08-26 15:05 ` Re: Time is off in PG server Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-26 15:22   ` Re: Time is off in PG server Ericson Smith <eric@did-it.com>
  2004-08-26 15:31     ` Re: Time is off in PG server Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-26 16:14       ` Ericson Smith <eric@did-it.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Ericson Smith @ 2004-08-26 16:14 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

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

Attachments:

eric.vcf (text/x-vcard, 313B) download begin:vcard fn:Ericson Smith n:Smith;Ericson org:Did-it.com;Programming adr:#304;;55 Maple Avenue;Rockville Center;NY;11570;USA email;internet:eric@did-it.com title:Web Developer tel;work:516-255-0500 tel;cell:646-483-3420 note:Nothing special! x-mozilla-html:FALSE url:http://www.did-it.com version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-08-26 16:14 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-26 13:47 Time is off in PG server Ericson Smith <eric@did-it.com>
2004-08-26 14:38 ` Jay A. Kreibich <jak@uiuc.edu>
2004-08-26 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-26 15:22   ` Ericson Smith <eric@did-it.com>
2004-08-26 15:31     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-26 16:14       ` Ericson Smith <eric@did-it.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox