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.