Postgres equivalent for TO_UTC_TIMESTAMP

What would be the Postgres equivalent for TO_UTC_TIMESTAMP_TZ function in oracle.

For the below query:

SELECT TO_UTC_TIMESTAMP_TZ('1998-01-01') FROM DUAL;

The result is "01-JAN-98 12.00.00.000000000 AM GMT" in oracle.

CodePudding user response：

I am not entirely sure what the Oracle function does, but I think this should be equivalent:

TO_TIMESTAMP('1998-01-01', 'yyyy-mm-dd') at time zone 'UTC' at time zone 'GMT'

CodePudding user response：

Append 'T00:00 UTC' and cast to timestamptz.

select ('1998-01-01'||'T00:00 UTC')::timestamptz;

Check:

select to_char(
 ('1998-01-01'||'T00:00 UTC')::timestamptz, 
 'yyyy-mm-dd hh24:mi:ss.us OF'
);

Result "1998-01-01 02:00:00.000000 02".
This is exactly the same as "01-JAN-98 12.00.00.000000000 AM GMT".

It might make sense to define a compatibility function.

create function to_utc_timestamp_tz(ts text) returns timestamptz immutable as
$$
 select (ts || 'T00:00 UTC')::timestamptz;
$$ language sql;