How can I work with am / pm mysql hours?


my query is about the management of standard hours colombia am / pm in the bd mysql manager.

That is to say: I want the user to see the time he wants to select in am / pm format through java, but I need to do operations with the hours, something similar to the handling of hours in excel:

example: 07:58:00 am + 10:58:00 pm = 06:56:00 am or 06:58:00 am + 11:58:00 pm + 02:00:00 pm = 08:56:00 p.m .

add a certain amount of hours, save your result either in hh: mm am or pm and then be displayed on the screen, I don't know how this would be done in mysql since here the handling of hh: mm: ss is 24 hh format .

With the following query I can obtain the sum of the hours of the same column, but in 24 hh format. (I would also like to know how to add the hh: mm: ss in different columns in the same row.)

SELECT SEC_TO_TIME(sum(TIME_TO_SEC(parada_tiemp_mp))) as totalhr_mp FROM materiaprima.

I would appreciate the help, if maybe there is a way either by a trigger or what logic would have to apply in java, thanks.


If you look at the documentation for MySQL 1 , specifically in the section on Functions for dates and times ; we have to:

The TIME_FORMAT() function can receive the following parameters:

  • The column or value that contains the timestamp / date to be formatted
  • A second parameter in the form of a flag that can be filled with:
    • %r which will give the format of hh:mm:ss AM/PM


I take the time from the NOW() function and format it with TIME_FORMAT


With an output like this:

07:56:56 PM

So your options are:

  • Save the data in the traditional 24-hour format and only format them for the moment they are output on the screen, when you need to do operations with them (I recommend more) , without having to think about formatting according to the convenience of the view that you want to print them.
  • Format them before storing them, passing the value that you get from the form through the aforementioned function (I recommend less)


  • Flags for formatting
Scroll to Top