Search code examples
postgresqldatetimeapache-kafkatimestampavro

Unix timestamp in milliseconds in PostgreSQL

I'm streaming data from Kafka topic to PostgreSQL in Avro format. Column time contains Unix time in following format:

"time": "1645196215"
"time": "1645196215.3"
"time": "1645196215.34"
"time": "1645196215.36"
"time": "1645196215.3799999"
"time": "1645196215.3999999"
"time": "1645196215.4199998"

If I take this 1645196215.36 value for example I can remove "." and add 0 this will get me 1645196215360 which can be converted select extract(epoch FROM 1645196215360) * 1000 in PostgreSQL.

How to round other types of numbers considering I have to pass it in Avro format in PostgreSQL datetime with milliseconds precision?

My idea is to add 000 when there is nothing after ., add 00 if there is one digit after . and so on...is there better way to deal with it?

Solution

  • I don't know why you are trying to do this in such a complicated fashion. You could simply call to_timestamp on the value:

    SELECT to_timestamp(1645196215.36);

       to_timestamp        
═══════════════════════════
 2022-02-18 15:56:55.36+01
(1 row)