How to compare string field as Date in postgresql?

Question

I have a string field in a table whose value is date format like: 2020-12-08T18:06:55.132Z or 2020-12-08T18:06:55.132+11:00.

How can I search this field based on date? Like select * from my_table where timestamp > '2020-12-08T00:00:00'.

Both are valid timestamps with time zone, why do you use a string (text/char/varchar) in the first place? — Frank Heikens
– Frank Heikens, Commented Jun 7, 2021 at 9:24

romainsalles · Accepted Answer · 2021-06-06 23:47:51Z

1

You can try something like:

SELECT *
FROM my_table
WHERE to_timestamp(timestamp,'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') > '2020-12-08T00:00:00'

EDIT:

Something even easier, which doesn't rely on your timestamp format:

SELECT *
FROM my_table
WHERE timestamp::timestamp > '2020-12-08T00:00:00'

edited Jun 6, 2021 at 23:47

answered Jun 6, 2021 at 23:25

romainsalles

2,14314 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joey Yi Zhao Over a year ago

I got this error ERROR: value for "YYYY" in source string is out of range DETAIL: Value must be in the range -2147483648 to 2147483647.

romainsalles Over a year ago

I've edited my answer for using "to_timestamp" instead of "to_date". Concerning your error, do you have the entry causing the issue? Because when I try with your example date (by putting '2020-12-08T18:06:55.132Z' instead of timestamp, it works)

romainsalles Over a year ago

I've added to my answer another query which doesn't rely on a given format, it may works better.

Collectives™ on Stack Overflow

How to compare string field as Date in postgresql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related