Skip to content
Advertisement

Insertion by removing ‘000000’ in the unix timestamp

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:

enter image description here

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))
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement