Date formatting in PostgreSQL

Question

I want to convert date to specific format and subtract 2 hours from the time:

date 
2018-06-07 23:37:10

Expected output:

07Jun2018 21:37:10

I have tried it, but I got the below error:

error:operator doesnt exist : Text -interval,might need to add explicit type cast.

SELECT '2018-06-07 23:37:10'::timestamp - INTERVAL '2 hours'; — Adam
– Adam, Commented Aug 30, 2018 at 17:45
How do i derive a new column from '07Jun2018 21:37:10 '(data type text) based on time stamp(when time <12 then 'AM' else 'PM) — user8545255
– user8545255, Commented Aug 30, 2018 at 18:43

Moseleyi · Accepted Answer · 2018-08-31 02:51:26Z

5

You can try using TO_CHAR() like this:

SELECT TO_CHAR('2018-06-07 23:37:10'::TIMESTAMP - INTERVAL '2 HOURS', 'DDMonYYYY HH24:MI:SS')

(changed to HH24:MI as Oto Shavadze suggested)

Here is the reference how to create patterns in case you need it in the future

Just remember that after the conversion the type of the value will be text so you won't be able to perform any other date-related functions (you have to do it before TO_CHAR()).

edited Aug 31, 2018 at 2:51

answered Aug 30, 2018 at 17:49

Moseleyi

2,9754 gold badges34 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oto Shavadze Over a year ago

Just HH24:MI instead of HH:mm

user8545255 Over a year ago

How do i derive a new column from '07Jun2018 21:37:10 '(data type text)based on time stamp(when time <12 then 'AM' else 'PM)

Moseleyi Over a year ago

If you read the reference you will see there's a pattern you can use with to_char() AM, am, PM, pm any of those will actually yield the AM/PM

Collectives™ on Stack Overflow

Date formatting in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related