CODEWITHSUNDEEP
postgresqltimestampsql-timestamp
Aki T
Aki T

Reputation: 666

Query DateTime Varchar in Nanoseconds level in Postgres

I have a Postgres table with a column, that has DateTime values as varchar.

date_column 
"2023/10/26 17:20:00.100100000"
"2023/10/26 17:20:00.100100000"

To filter this column in microsecond level, I use a SQL query like below.

SELECT * FROM date_data
 WHERE TO_TIMESTAMP(date_column , 'YYYY/MM/DD HH24:MI:ss.US')  
 BETWEEN  TIMESTAMP '2023-01-01 09:09:09.000000000' AND TIMESTAMP '2024-01-11 09:09:09.000000000';

As in the above query, I use the TO_TIMESTAMP method to convert the DateTime varchar to TimeStamp type. My issue is Postgres doesn't support converting to Timestamp in nanosecond level. It supports only upto micrsecond level. 'YYYY/MM/DD HH24:MI:ss.US'

Upvotes: 1

Views: 183

Answers (1)

Zegarek
Zegarek

Reputation: 26586

As you already pointed out, nano (1e-9) is beyond PostgreSQL precision limit of micro (1e-6). You can consider timestamp9 extension that offers a more granular, nanosecond-precise version of timestamp.

Otherwise, anything above the limit has to get extracted to a separate column or bundled into an additional field of a custom type, neither of which is pretty: demo

create type timestamp_plus_nano as (ts timestamp, nano smallint);
select ('2023-01-01 09:09:09.123456',789)::timestamp_plus_nano;

Both of these force you to compare 4 values/fields against 2 instead of 2 against 1, which isn't very convenient.

Upvotes: 2

Related Questions