CODEWITHSUNDEEP
postgresqltimestamp
Oliniusz
Oliniusz

Reputation: 503

Postgres: incorrect sorting if timestamp in smtp format

I am working on a project where we send some data using SMTP and I have some weird issues with sorting timestamps if they are already stored in an smtp format like 'Dy, FMDD Mon YY HH24:MI:SS TZ' (for example 'Fri, 18 Aug 17 14:15:26 UTC').

An isolated case, sorting using smtp_message_time:

psql=> select
             time,
             to_char(time, 'Dy, FMDD Mon YY HH24:MI:SS TZ') as smtp_time
         from
             messages
         order by
             smtp_time;

            time            |          smtp_time
----------------------------+-----------------------------
 2017-08-18 14:15:26.537+00 | Fri, 18 Aug 17 14:15:26 UTC
 2017-08-18 14:17:33.57+00  | Fri, 18 Aug 17 14:17:33 UTC
 2017-07-10 14:11:46.035+00 | Mon, 10 Jul 17 14:11:46 UTC
 2017-07-10 14:21:27.032+00 | Mon, 10 Jul 17 14:21:27 UTC
 2017-07-10 14:21:40.541+00 | Mon, 10 Jul 17 14:21:40 UTC
 2017-07-10 14:23:01.928+00 | Mon, 10 Jul 17 14:23:01 UTC
 2017-07-10 14:23:15.508+00 | Mon, 10 Jul 17 14:23:15 UTC
 2017-07-10 14:24:14.322+00 | Mon, 10 Jul 17 14:24:14 UTC
 2017-07-10 14:26:21.426+00 | Mon, 10 Jul 17 14:26:21 UTC
 2017-07-10 14:35:38.044+00 | Mon, 10 Jul 17 14:35:38 UTC
 ....
 2017-08-16 16:03:29.214+00 | Wed, 16 Aug 17 16:03:29 UTC
 2017-08-16 16:04:16.037+00 | Wed, 16 Aug 17 16:04:16 UTC
 2017-08-16 16:05:07.444+00 | Wed, 16 Aug 17 16:05:07 UTC
 2017-07-26 13:49:13.217+00 | Wed, 26 Jul 17 13:49:13 UTC
(96 rows)

I found the issue after spotting negative intervals in some final results.

  1. Is there any logical explanation for that behaviour?
  2. Is there anything I am missing or I can do to have the format parsed correctly?

If possible, I would rather operate on the smtp format as my first choice rather than on the Postgres one as it is already stored that way and some code reworking would need to be done.

On a side note, I had already noticed I needed to use interval(timestamp with time zone, timestamp with time zone) to have the timezone taken into account, I also noticed that I could put different days of a week and results stayed the same.

Upvotes: 1

Views: 398

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246298

PostgreSQL is surprisingly flexible in the input it accepts for timestamps.

SELECT 'Fri, 18 Aug 17 14:15:26 UTC'::timestamptz;

      timestamptz       
------------------------
 2017-08-18 16:15:26+02
(1 row)

So you could just

ORDER BY smtp_time::timestamptz

Upvotes: 1

Related Questions