How to set PostgreSQL's internal clock?

Hi.

I get the PostgreSQL internal time using this command: "SELECT CURRENT_TIMESTAMP". But since I installed PostgreSQL 8.0 Beta 1 on my windows I get different times between system time and PostgreSQL time.
How to set PostgreSQL's internal clock?

Thanks.
pepoclvAsked:
Who is Participating?
 
ldbkuttyCommented:
Might be because the time set to some other timezone specification.

SET TIME ZONE LOCAL;  
will set the time zone to your local time zone (the one that the server's operating system defaults to) and then run

SELECT CURRENT_TIMESTAMP;
0
 
pepoclvAuthor Commented:
Look at this example:

Windows system time displays: 09/02/2004 4:53pm

PostgreSQL command "SELECT CURRENT_TIMESTAMP" returns: 09/02/2004 2:24pm
0
 
brozzisCommented:

AFAIK there is no internal clock in postgreSQL., it shouldn't make any sense.
I suppose it's the reason why it is still a beta version.

On my 7.4.2 works as expected, returning the system time.

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
brozzisCommented:

In that case I'd expect a different hour, or half hour, not a complete different time.

...I've noticed right now that between 4:53 and 2:24  there's a difference of 2h29m,
that could lead the solution to a matter of time zones...

0
 
earth man2Commented:
You really need to define local timezone in postgresql.conf.  For me in GB I always use the following.

If you don't it returns UTC AFAIK

# - Locale and Formatting -

datestyle = 'sql, dmy'
timezone = 'Europe/London'              # actually, defaults to TZ environment setting
0
 
pepoclvAuthor Commented:
Now I know that the problem is time zone. I tryed to set local timezone doing this: SET TIME ZONE 'GMT-3'; but it did not work.
When I run SELECT CURRENT_TIMESTAMP; it returns "2004-09-03 12:47:28.093-00".
0
 
earth man2Commented:
dev=# set session timezone to -3;
SET
dev=# select current_timestamp, current_timestamp at time zone 'BST';
          timestamptz          |          timezone          
-------------------------------+----------------------------
 03/09/2004 10:05:51.652133-03 | 03/09/2004 14:05:51.652133
(1 row)

0
 
earth man2Commented:
I think pg_tzset  look in directory /usr/share/postgresql/timezone   GMT-3 is in the "Etc" subdirectory so this works.

dev=> set session timezone to 'Etc/GMT-3';
SET
dev=> select current_timestamp, current_timestamp at time zone 'BST';
           timestamptz           |         timezone          
---------------------------------+---------------------------
 15/09/2004 15:12:13.14708 GMT-3 | 15/09/2004 13:12:13.14708
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.