I’m inserting some data for python that comes from a GET in API FLASK like JSON, for some reason when python will insert this data or maybe postgres itself, it is removing the 000000
Data json:
{ "datecollect": 1585983000000 }
Insert py:
conn.execute('''INSERT INTO "TBLNAME" (value1,value2,value3,value4,value5) VALUES ('{0}','{1}','{2}',to_timestamp({3}) AT TIME ZONE 'UTC',current_timestamp)'''. format(server,forecastmemory,levelerror,datecollect))
Original date:
select to_timestamp(1585983000000) AT TIME ZONE 'UTC' --> 52227-10-20 17:20:00
Date that python is inserting to pgsql:
select to_timestamp(1585983) AT TIME ZONE 'UTC' --> 1970-01-19 08:33:03
Conversion correct:
Even using the website’s timezone, it returns with a problem select to_timestamp(1585983000000) AT TIME ZONE 'GMT-03:00' --> 52227-10-20 20:20:00
Advertisement
Answer
Your timestamp value (datecollect
) is apparently in milliseconds; you just need to divide it by 1000 to convert it from milliseconds to seconds:
SELECT TO_TIMESTAMP(1585983000000/1000) AT TIME ZONE 'UTC'
Output:
2020-04-04 06:50:00
So for your query:
conn.execute('''INSERT INTO "TBLNAME" (value1,value2,value3,value4,value5) VALUES ('{0}','{1}','{2}',to_timestamp({3}) AT TIME ZONE 'UTC',current_timestamp)'''. format(server,forecastmemory,levelerror,datecollect/1000))