Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

PostgreSQL: format timestamp with T delimiter between time and date

What I expect:

SELECT to_char(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DDTHH24-MI-SS')

to produce:

2022-11-11T11-29-51

But it produces:
enter image description here

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

I’ve tried several options, but neither of them does the trick:
enter image description here
Query for convenience:

SELECT to_char(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DDTHH24-MI-SS') as plain,
       to_char(current_timestamp AT TIME ZONE 'UTC', E'YYYY-MM-DD\'T\'HH24-MI-SS') as with_quotes,
       to_char(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD\THH24-MI-SS') as with_backslash,
       to_char(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DDT HH24-MI-SS') as with_space

It looks like that T letter gets parsed as part of pattern and then got skipped in a plain sample.
Any idea how to get desired output?

>Solution :

Use double quotes to escape a constant values:

Quote from the manual

Ordinary text is allowed in to_char templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains template patterns. For example, in '"Hello Year "YYYY', the YYYY will be replaced by the year data, but the single Y in Year will not be

to_char(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24-MI-SS')
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading