6

How can we convert the time in AM/PM to 24-hrs format. For eg. (1:30 PM) should be converted to (13:30).

Vicky
  • 1,657
  • 6
  • 23
  • 33

5 Answers5

8

Dates / Times are stored in mysql the same way regardless of how they are formatted.

I believe what you want to do is retrieve the date in a specified format.

The DATE_FORMAT() will do this for you.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

%r and %T are 12 hour and 24 hour time formats respectively.

tschaible
  • 7,635
  • 1
  • 31
  • 34
  • I tried giving: select time_format('01:56:39 PM','%T') But it's returning same 01:56:39 PM and giving a warring as "Truncated incorrect time value: '01:56:39 PM' " – Vicky Jun 13 '09 at 06:28
  • 3
    The value you are passing in to time_format is still a string, and mysql expects to do it's work on a date value. Try converting to a date first, like select time_format(str_to_date('01:56:39 PM','%r'),'%T'); – tschaible Jun 13 '09 at 12:38
  • Hi, Can you please update your comment to your answer..bcoz the comment is what helped.. the answer to my query and possibly the user's was the `str_to_date` method... Thanks!! – Arcanyx Feb 28 '17 at 07:40
6

Hi all I think this will help you

select STR_TO_DATE('8:25 PM', '%l:%i %p' )

the result will be 

>20:25:00
Rushabh Master
  • 450
  • 4
  • 13
2

You could use MySQL's DATE_FORMAT http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Sander
  • 19
  • 2
1

This may help:

Table: records
Column date: Type date,
Column hours: Type string (am/pm format)


SELECT
date,
hours,
CONCAT(date, ' ', IF(LOCATE('pm',hours) > 0, ADDTIME(TIME(REPLACE(hours, ' pm','')), '12:00:00'),  TIME(REPLACE(hours, ' am',''))))
FROM records
Rajdeep Singh
  • 17,621
  • 6
  • 53
  • 78
user1058982
  • 89
  • 1
  • 4
1

1:=> print( date("H:i:s", strtotime("1:30 pm")) );

output: 13:30:00

Gaurang
  • 1,928
  • 18
  • 12