Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

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.
0
pepoclv
Asked:
pepoclv
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now