PSQL timestamp – how to include microseconds?

Question

How can I create an SQL timestamp from an ISO timestamp that includes microseconds? e.g. select to_timestamp('2021-08-29T16:32:25.336239Z','yyyy-mm-ddThh24:mi:ss.usZ') will result in 2021-08-29 16:32:25 – microseconds precision lost.

Answer

You can use ISO timestamps directly:

select TIMESTAMP '2021-08-29T16:32:25.336239Z' as ts

The TIMESTAMP 'YYYY-MM-DD HH:mm:ss.nnnnnn+offset' is the standard ANSI SQL format, which PG supports, and they also support ISO8601 in addition to it, since the difference is minimal. When using as a parameter from a whatever language you’re using, you don’t use the TIMESTAMP keyword, because it will result in a syntax error.

Accepted Answer

You can use ISO timestamps directly:select TIMESTAMP '2021-08-29T16:32:25.336239Z' as tsThe TIMESTAMP 'YYYY-MM-DD HH:mm:ss.nnnnnn+offset' is the standard ANSI SQL format, which PG supports, and they also support ISO8601 in addition to it, since the difference is minimal. When using as a parameter from a whatever language you&#8217;re using, you don&#8217;t use the TIMESTAMP keyword, because it will result in a syntax error.

Advertisement

Answer