CODEWITHSUNDEEP
postgresql
Ms13
Ms13

Reputation: 151

how to convert 12 hours timestamp format to 24 hours timestamp format in postgres?

how to convert 12 hours timestamp format to 24 hours timestamp format in postgres? like '2016-07-01 01:12:22 PM' to '2016-07-01 13:12:22'

Upvotes: 6

Views: 30638

Answers (3)

NileshDa
NileshDa

Reputation: 161

Using PostgreSQL:

To convert 24 hours to 12 hours:

select to_char( to_timestamp ( '13:00:00', 'HH24:MI:SS' ) , 'HH12:MI:SS PM' )
    from emp_table;

To convert 12 hours to 24 hours:

select to_char( to_timestamp ( '11:00:00' , 'HH12:MI:SS' ) , 'HH24:MI:SS AM' )
    from emp_table;

Upvotes: 16

user330315
user330315

Reputation:

Values in a timestamp (or date, time, integer or any type non-character type) are not stored in any specific format.

Any format you see is applied by the application you are using to display the values - typically the SQL client you are using.

There are two ways to change that:

  1. Configure your SQL client to use a different timestamp format for display (how you do that depends on the SQL client you are using - check its manual)

  2. Use the the_char() function to format your timestamp value throug SQL

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

More details on the available formats can be found in the manual: https://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

Upvotes: 3

Kristo Mägi
Kristo Mägi

Reputation: 1694

Easily, just cast it like follows:

SELECT '2016-07-01 01:12:22 PM'::timestamp;
      timestamp
---------------------
 2016-07-01 13:12:22
(1 row)

Upvotes: 0

Related Questions