EXTRACT epoch ends in a syntax error

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

CodePudding user response：

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.