Postgres: How to sort by string date with timezone?
Answer a question In my PostgreSQL DB, I store JSONB, where one of the JSON nodes is a Date string in the following format: YYYY-MM-DD , then hours in 24-hour format, minutes and seconds, followed by
Answer a question
In my PostgreSQL DB, I store JSONB, where one of the JSON nodes is a Date string in the following format:
YYYY-MM-DD , then hours in 24-hour format, minutes and seconds, followed by the timezone offset, like this:
2003-06-30 05:51:54+00:00 2003-06-30 14:25:45+10:00
When I do a sort on the date in ASC order, however, the times are not properly sorted by the timezone offset and I think it's because PostgreSQL is still looking at the dates as if they were strings.
SELECT header.id,
header.data ->> 'date' as "Date"
FROM all_messages.sci_math_headers header
WHERE header.data ->> 'msgid' = 'xyz'
OR header.data ->> 'ref' like '%xyz>%'
ORDER BY header.data ->> 'date' ASC
LIMIT 20;
So even though 2003-06-30 14:25:45+10:00 should be first, it's coming as a second result. 2003-06-30 05:51:54+00:00 2003-06-30 14:25:45+10:00
Here is an example screenshot:
Is there any way to convert each date string to timestamp directly in my query and sort it properly?
Answers
Convert your strings to the true timestamps:
SELECT header.id,
header.data ->> 'date' as "Date"
FROM all_messages.sci_math_headers header
WHERE header.data ->> 'msgid' = 'xyz'
OR header.data ->> 'ref' like '%xyz>%'
ORDER BY (header.data ->> 'date')::timestamptz ASC -- Changes here
LIMIT 20;
should to work.
更多推荐
所有评论(0)