How do I get the current unix timestamp from PostgreSQL?

  • Unix timestamp is the number of seconds since midnight UTC January 1, 1970.

    How do I get the correct unix timestamp from PostgreSQL?

    When comparing to currenttimestamp.com and timestamp.1e5b.de I don't get the expected time from PostgreSQL:

    This returns the correct timestamp:

    SELECT extract(epoch from now());
    

    While this doesn't:

    SELECT extract(epoch from now() at time zone 'utc');
    

    I live in time zone UTC +02. What is the correct way to get the current unix timestamp from PostgreSQL?

    This returns the correct time and time zone:

    SELECT now();
                  now
    -------------------------------
     2011-05-18 10:34:10.820464+02
    

    Another comparison:

    select now(), 
    extract(epoch from now()), 
    extract(epoch from now() at time zone 'utc');
                  now              |    date_part     |    date_part
    -------------------------------+------------------+------------------
     2011-05-18 10:38:16.439332+02 | 1305707896.43933 | 1305700696.43933
    (1 row)
    
    Unix timestamp from the web sites:
    1305707967
    
  • In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

    Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

    select extract(epoch from now());
    

    That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

    Things only get complicated when you have a timestamp field.

    When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

    If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

    select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
         , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;
    
    /*
    |timestamptz           |timestamptz           |
    |:---------------------|:---------------------|
    |2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
    */
    

    DBFiddle

    But, note the following confusing behaviour:

    set timezone to 0;
    
    values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
        , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);
    
    /*
    |column1|column2               |
    |------:|:---------------------|
    |      1|1970-01-01 00:00:00+00|
    |      2|1970-01-01 00:00:00+00|
    */
    

    DBFiddle

    This is because:

    PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

    Any idea how to convert the resulting decimal into an integer without decimal point (I mean merging the number and the decimal as one big integer). Thanks.

    Like this but I'm sure you don't really want to do that. Perhaps you want to multiply by a power of ten and strip any remaining decimals?

    @W.M. Maybe like this? `SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);`

  • SELECT extract(epoch from now() at time zone 'utc');
    

    doesn't return the correct timestamp because postgres timezone conversion throws away timezone information from the result:

    9.9.3. AT TIME ZONE

    Syntax: timestamp without time zone AT TIME ZONE zone
    Returns: timestamp with time zone
    Treat given time stamp without time zone as located in the specified time zone

    Syntax: timestamp with time zone AT TIME ZONE zone
    Returns: timestamp without time zone
    Convert given time stamp with time zone to the new time zone, with no time zone designation

    afterwards, extract looks at timestamp without time zone and considers it to be a local time (although it is already utc in fact).

    The correct way would be:

    select now(),
           extract(epoch from now()),                                          -- correct
           extract(epoch from now() at time zone 'utc'),                       -- incorrect
           extract(epoch from now() at time zone 'utc' at time zone 'utc');    -- correct
    
              now                  |    date_part     |    date_part     |    date_part
    -------------------------------+------------------+------------------+------------------
     2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691
    (1 row)
    

    In the last line the first at time zone performs the conversion, the second one assigns new time zone to the result.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM