Postgresql to_char add characters to date

Question

I want to select dates with this format: 2018-11-25T16:00:00Z

So i tried to do this:

select to_char(registration_date, 'YYYY-MM-DDTHH24:MI:SSZ')
from ...

but its output is:

2018-11-01STH24:17:35Z 

The char 'T' changes the date output instead of just print it in the middle. I tried to add space next to the char 'T' like this :

select to_char(registration_date, 'YYYY-MM-DDT HH24:MI:SSZ')

and it works, but i need the date without spaces.

How can I add the char 'T' to the middle of the date, and the output will be the same as the first example?

Answer 1

Yes, that's a really interesting one. I could reproduce it:

SELECT to_char('2018-11-01 15:21:24'::timestamp, 'YYYY-MM-DDTHH24:MI:SSZ')

gives

2018-11-01STH24:21:24Z  -- note the S

And

SELECT to_char('2018-11-04 15:21:24'::timestamp, 'YYYY-MM-DDTHH24:MI:SSZ')

gives

2018-11-04THH24:21:24Z -- note that THH still seems to remain as in the format string

---

The problem here is the format value TH. This string is interpreted as the "ordinal number suffix". It adds the suffix "ST", "ND", "RD", and "TH" for "first", "second", "third", "fourth" and others. In your format string this is combined from your T and the format string HH24.

See:

SELECT to_char('2018-11-01 15:21:24'::timestamp, 'YYYY-MM-DDTH')

gives

2018-11-01ST 

and

SELECT to_char('2018-11-04 15:21:24'::timestamp, 'YYYY-MM-DDTH')

gives

2018-11-04TH

So in reality your examples above can be explained. The S is in reality a ST, the H24 is an uninterpreted rest of the THH24 (where the TH is already taken for the ordinal suffix). Same with second example where the TH is converted in a TH again, so it seems to be ignored.

---

Your solution is putting the T into double quotes:

SELECT to_char('2018-11-01 15:21:24'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SSZ')

which gives:

2018-11-01T15:21:24Z

demo:db<>fiddle

Postgres date formatting