EXTRACT epoch ends in a syntax error - Postgres

Question

I had the need, to calculate the difference of two timestamp in miliseconds. This is the approach i made:

-- calculate the differenz of two timestamps in milliseconds
DO $$
  declare _t1          timestamp;
  declare _t2          timestamp;
  declare _extract_ms  double precision;
BEGIN
  _t1 = now();
  _t2 = (_t1 - INTERVAL '1 HOUR');
  -- _extract_ms = EXTRACT(EPOCH FROM TIMESTAMP _t1::timestamp) - EXTRACT(EPOCH FROM TIMESTAMP _t2::timestamp); -- UNCOMMENT 01
  -- _extract_ms = EXTRACT(EPOCH FROM date_trunc('milliseconds', _t1)) - EXTRACT(EPOCH FROM date_trunc('milliseconds', _t2)); -- UNCOMMENT 02

  RAISE NOTICE '_t1=%  _t2=%  _extract_ms=%', _t1, _t2, _extract_ms;
END;
$$ LANGUAGE plpgsql;

0 - Executing the code as shown before, results in the following output:

HINT: _t1=2022-02-22 14:14:59.627456 _t2=2022-02-22 13:14:59.627456 _extract_ms=<NULL>

1 - Now uncomment the line marked with "UNCOMMENT 01" and execute the code again ends with:

ERROR: Syntax error at »_t1«
LINE 9:    _extract_ms = EXTRACT(EPOCH FROM TIMESTAMP _t1::timestamp...

2 - Now comment line marked with "UNCOMMENT 01" again and uncomment line marked with "UNCOMMENT 02" and execute it again:

HINT: _t1=2022-02-22 14:28:43.161478 _t2=2022-02-22 13:28:43.161478 _extract_ms=3600

This is the expected result!

If I simply run select EXTRACT(EPOCH FROM TIMESTAMP now()); I get

ERROR: Syntax error at »now«
LINE 1: select EXTRACT(EPOCH FROM TIMESTAMP now());

I stumpled over pgplsql: SELECT EXTRACT(...) functioniert nicht and thought this is the solution - not for me.

So, can anyone explain me why variant 1 and even the simply select-statement fails? Thanks in advance.

Answers read: How to convert date format into milliseconds in postgresql?, Calculate difference between dates - Postgres

EXTRACT(EPOCH FROM _t1) - the timestamp keyword is not part of the extract syntax and the cast is not needed — user330315
– user330315, Commented Feb 22, 2022 at 14:04
@a_horse_with_no_name. TIMESTAMP is part of the syntax if you are using timestamp string e.g. select extract(epoch from timestamp '02/22/2022 7:50'); 1645516200 — Adrian Klaver
– Adrian Klaver, Commented Feb 22, 2022 at 15:49
@AdrianKlaver: yes, but it belongs to the constant, not to the defined parameters of extract() — user330315
– user330315, Commented Feb 22, 2022 at 15:51
@a_horse_with_no_name, I know that but it is not obvious if one looks at the docs Extract or the linked example. — Adrian Klaver
– Adrian Klaver, Commented Feb 22, 2022 at 16:01

Adrian Klaver · Accepted Answer · 2022-02-22 16:07:14Z

1

The issue is this:

select extract(epoch from timestamp '02/22/2022 7:50'); 
 date_part  
------------
1645516200

select extract(epoch from '02/22/2022 7:50'::timestamp);
 date_part  
------------
 1645516200

select EXTRACT(EPOCH FROM TIMESTAMP now());
ERROR:  syntax error at or near "now"
LINE 1: select EXTRACT(EPOCH FROM TIMESTAMP now());

select timestamp now();
ERROR:  syntax error at or near "("
LINE 1: select timestamp now();

select EXTRACT(EPOCH FROM now());
     date_part     
-------------------
 1645545952.426751

The TIMESTAMP is a CAST of the timestamp string to a timestamp type. The same thing is done by the '02/22/2022 7:50'::timestamp. In either case the cast is looking for a string. The now() and _t1/ _t12 in your case are already timestamp types so you get the error. So if you are using a value that is a timestamp/timestamptz/date type already don't use TIMESTAMP to cast it.

edited Feb 22, 2022 at 16:07

answered Feb 22, 2022 at 15:58

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wolfram Kulterer Over a year ago

Thank you for your explaination. I agree with you, one doesn't see at a glance in the docs. I tried a lot of different syntax, but always with the keyword TIMESTMAP. Anyway, you helped me and again thank you.

Collectives™ on Stack Overflow

EXTRACT epoch ends in a syntax error - Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related