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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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".
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)
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/time zone 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
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
ASKER
Windows system time displays: 09/02/2004 4:53pm
PostgreSQL command "SELECT CURRENT_TIMESTAMP" returns: 09/02/2004 2:24pm