postgres - date time is automatically converted-CodePudding

Using vscode in debug mode, when I hover over a date field, it show as the below image.

but when I logged it out, it get converted to

"execution_date":"2021-12-02T20:23:48.322Z"

which is minus 7 hour. The field is stored in postgres database on AWS RDS as timestamp, running show timezone; returns UTC and I am using VSCode in GMT 7 time. How can I fix this because the date get changed and used to call api, so the returned result would be incorrect.

CodePudding user response：

This is not a complete answer as that would depend on more information. Instead it is an explanation of what is going on that may help you troubleshoot:

set TimeZone = UTC;
show timezone;
 TimeZone 
----------
 UTC
--Show that timestamp is taken at UTC
 select now();
              now              
-------------------------------
 2021-12-05 18:23:38.604681 00

--Table with timestamp and timestamptz to show different behavior.
create table dt_test(id integer, ts_fld timestamp, tsz_fld timestamptz);

--Insert local time 'ICT'
insert into dt_test values (1, '2021-12-03 03:23:48.322 07', '2021-12-03 03:23:48.322 07');

--The timestamp entry ignores the time zone offset, while the timestamptz uses it to rotate to UTC as '2021-12-03 03:23:48.322 07' is same as '2021-12-02 20:23:48.322 00'
select * from dt_test ;
 id |         ts_fld          |          tsz_fld           
---- ------------------------- ----------------------------
  1 | 2021-12-03 03:23:48.322 | 2021-12-02 20:23:48.322 00


--timestamp takes the value as at 'ICT' and then rotates it to the current 'TimeZone' UTC. The timestamptz takes the value at UTC at rotates it to 'ICT'
select ts_fld AT TIME ZONE 'ICT', tsz_fld AT TIME ZONE 'ICT' from dt_test ;
          timezone          |        timezone         
---------------------------- -------------------------
 2021-12-02 20:23:48.322 00 | 2021-12-03 03:23:48.322

I am guessing at some point in the process to get the value for the API the code is taking the timestamp value and applying AT TIME ZONE 'ICT' either in the database or downstream using some equivalent procedure.

CodePudding user response：

I have found the problem, it's is related to how Sequelize and postgres deal with timestamp without timezone. If you have the same problem like me, please refer to the following link: https://github.com/sequelize/sequelize/issues/3000