CODEWITHSUNDEEP
postgresql
Moseleyi
Moseleyi

Reputation: 2879

PostgreSQL - timestamp results in 12-hour format but need 24-hour

I am working on synchronisation between our database and CRM, and the CRM needs the data type to be TIMESTAMP but when I execute this simple query

SELECT
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP::TIMESTAMP

I'm getting: 

2018-06-13 08:37:05.944909
2018-06-13 01:37:05.944909

I'm 5 hours behind UTC, but I expected the timestamp to return 2018-06-13 13:37:05.944909

When 2018-06-13 01:37:05.944909 is inserted into the CRM it thinks it's 1:37AM and that of course messes up a lot of other stuff.

I know I could convert this using TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS') but that will change the data type to TEXT, which I can't have.

Is there a way to force PostgreSQL to display timestamps in 24-hour format without losing the data type?

Upvotes: 0

Views: 4817

Answers (1)

Pawan Sharma
Pawan Sharma

Reputation: 319

Use the the_char() function to format your timestamp value through PSQL

select to_char(the_column, 'yyyy-mm-dd hh24:mi:ss') from the_table

Upvotes: 1

Related Questions