Mar 072013
 

If you are dealing with multiple different clients, each with its own clock, it pays of to have central time-keeping device. And, if your program uses Microsoft SQL Server, you can use its GETUTCDATE() function to retrieve current server time:

GETUTCDATE() function is ideal for this purpose. It returns UTC time so you don’t need to worry about time zones and daylight-saving time. Your database and programs can keep time in UTC. Conversion to local time is done only when displaying data to user.

You can get current time either by doing SELECT query or by inserting time directly into database:

SELECT GETUTCDATE();
INSERT INTO Foo(Bar) VALUES(GETUTCDATE());

However, this function is very SQL Server specific. For example, you will not find it in PostgreSQL. Yes, I know that there are other functions that can do the same. But this means that your application needs to do one query for SQL Server and another for PostgreSQL. It would be fantastic if same function could be used in both databases.

Well, you can. Only thing we need is a new function:

CREATE OR REPLACE FUNCTION GETUTCDATE() RETURNS TIMESTAMP AS '
    SELECT CAST(LOCALTIMESTAMP AT TIME ZONE ''UTC'' AS TIMESTAMP);
' LANGUAGE SQL VOLATILE;

  2 Responses to “UTC time in PostgreSQL”

Comments (2)
  1. It’s seemingly more like:
    SELECT CURRENT_TIMESTAMP AT TIME ZONE ‘UTC’;

    1) CURRENT_TIMESTAMP otherwise you’ll get several hours back,
    2) No CAST necessary

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>