CODEWITHSUNDEEP
postgresqldbeaverpostgresql-11
Gabriel
Gabriel

Reputation: 5734

Casting ISO8601 date text to Timestamp

I'm working with some dates in ISO 8601 and when casting to timestamp I get some oddities in the millisecond:

SELECT '2020-05-15T19:01:59.002+00:00'::timestamp
2020-05-15 19:01:59:02 <--- wrong milliseconds

SELECT '2020-05-15T19:01:59.022+00:00'::timestamp
2020-05-15 19:01:59:22 <--- wrong milliseconds

SELECT '2020-05-15T19:01:59.22+00:00'::timestamp
2020-05-15 19:01:59:220 <--- ok, but I'm scared

I tried to use to_timestamp() but cant find a way to workaround the T in the string. Any guidance is appreciated!

Upvotes: 7

Views: 9294

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

What you are seeing appears to just be an artifact in the view of whatever technology you are using to access Postgres. As shown in this demo, in fact your cast to timestamp is working correctly. Consider the following:

WITH yourTable AS (
    SELECT '2020-05-15T19:01:59.002+00:00'::timestamp AS ts
)

SELECT
    ts,
    EXTRACT(milliseconds FROM ts) AS millis
FROM yourTable;

The above query correctly generates 59,002 as being the number of milliseconds in the timestamp.

Upvotes: 8

Related Questions