Link to home
Start Free TrialLog in
Avatar of pepoclv
pepoclv

asked on

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.
Avatar of pepoclv
pepoclv

ASKER

Look at this example:

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

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

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.

ASKER CERTIFIED SOLUTION
Avatar of ldbkutty
ldbkutty
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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

SOLUTION
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pepoclv

ASKER

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

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