PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Extract epoch from column of timestamps in Postgres

What is the syntax to get the epoch from a timestamp column with time zone.

I can use with time zone in the following way and it works as expected

select extract(epoch from timestamp with time zone '1970-01-01 00:00:00.000 +00') ;

returns 0

select extract(epoch from timestamp with time zone '1970-01-01 00:00:00.000 -0500') ;

returns 18000.000000

Great!

But this returns the same epoch for both:

select extract(epoch from ts ) 
from(
    select to_timestamp(ts, 'yyyy-mm-dd h24:mi:ss') ts
    from (
        select unnest (array[
        '1970-01-01 00:00:00.000 +00',
        '1970-01-01 00:00:00.000 -0500'
        ]) ts
    )
);

18000.000000
18000.000000

Where do I put the with time zone when I am extracting the epoch for a column of timestamps?

I have tried the following with no success:

  • epoch from ts with time zone –fails SQL Error [42601]: ERROR: syntax error at or near "with"
  • epoch from ts::timestamp with time zone — runs, but gives the same values for both
Tags:

2

Answers


  1. 0

    This might be what you’re looking for

    select extract(epoch from ts) 
from(
  select ts::TIMESTAMPTZ at time zone 'UTC' ts
   from (
       select unnest (array[
       '1970-01-01 00:00:00.000+00',
       '1970-01-01 00:00:00.000-0500'
       ]) ts
   ) a
) b;

    From your query; the timezone needs to be identified through the cast first before using the epoch.

    Login or Signup to reply.
  2. 0

    In PostgreSQL, when you convert a text timestamp to a timestamp with time zone, the conversion will use the current time zone setting unless you explicitly specify the time zone in the conversion.

    However, since your timestamps already include the time zone information, you can directly cast them to timestamptz without using to_timestamp:

    SELECT extract(epoch FROM ts::timestamptz) 
FROM (
    SELECT unnest(ARRAY[
        '1970-01-01 00:00:00.000 +00',
        '1970-01-01 00:00:00.000 -0500'
    ]) AS ts
) subquery;

    This will correctly interpret the time zone information included in the timestamps and provide the correct epoch values.

    0.000000
18000.000000

    Explanation:

    The first timestamp ‘1970-01-01 00:00:00.000 +00‘ corresponds to epoch 0.

    The second timestamp ‘1970-01-01 00:00:00.000 -0500‘ is 5 hours behind UTC, which corresponds to an epoch of 18000 seconds.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search