Forum OpenACS Q&A: postgresql current_timestamp

Collapse
1: postgresql current_timestamp
Posted by Gilbert Wong on 05/24/01 01:55 AM
I ran this command in the psql interpreter:

select current_timestamp;

and I compared it to the system's date function.  PostgreSQL's time
is about 3 hours ahead of the system time!  How can I synchronize the
times?  Thanks!

Collapse
2: Response to postgresql current_timestamp (response to 1)
Posted by Don Baccus on 05/24/01 06:45 AM
Ummm...I've never seen that.  What does the "-nn" value appended to the timestamp say, is it the same as for your timezone?  Is it off by three, perhaps?
Collapse
3: Response to postgresql current_timestamp (response to 1)
Posted by Gilbert Wong on 05/24/01 06:37 PM
It is -04.  I'm in the pacific time zone.  what does the -04 mean?  Thanks.
Collapse
4: Response to postgresql current_timestamp (response to 1)
Posted by Simon Carstensen on 05/24/01 06:40 PM
It means that you're 4 hours behind UTC.
Collapse
5: Response to postgresql current_timestamp (response to 1)
Posted by Gilbert Wong on 05/24/01 06:55 PM
Thanks.  That's not the right time zone for me.  I'm in the Pacific Time zone and should be GMT-8.  How can I change this in PostgreSQL?  My system time is correct.
Collapse
6: Response to postgresql current_timestamp (response to 1)
Posted by Simon Carstensen on 05/24/01 07:35 PM
PGTZ sets the default time zone.

Add this to your postgres user's .bash_rc file:

"export PGTZ=DEFAULT"

DEFAULT sets the time zone to your local time zone (the one that your operating system defaults to).

Collapse
7: Response to postgresql current_timestamp (response to 1)
Posted by Gilbert Wong on 05/24/01 08:06 PM
Simon,

That doesn't work.  It still registers the timezone as -04.  HPUX is a bitch to work with.  Aghhhh! :)

Collapse
8: Response to postgresql current_timestamp (response to 1)
Posted by Simon Carstensen on 05/24/01 08:37 PM
Oh, though you were using Linux..

Anyway, are you sure PGTZ or TZ are set correctly (I don't know if my Linux syntax will work on your machine - have you changed it so it'll work on HP-UX?)?

Collapse
9: Response to postgresql current_timestamp (response to 1)
Posted by Gilbert Wong on 05/25/01 07:33 AM
I think I set the environment variables correctly, but these might not be the correct environment variables for HPUX.  hmmm...  I really wish we could use linux boxes.  :)
Collapse
10: Response to postgresql current_timestamp (response to 1)
Posted by Simon Carstensen on 05/25/01 07:41 AM
Sorry, I don't know anything about Postgres on HP-UX. You should read the "PostgreSQL Administrator's Guide" though.
Collapse
11: Response to postgresql current_timestamp (response to 1)
Posted by Simon Carstensen on 05/25/01 07:46 AM
Also...

Search for "time zone".

And ask your question on the postgres mailing-list and remember to report back if you find the answer.