Question:
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.
Answer:
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:
-
%rwhich will give the format ofhh:mm:ss AM/PM
-
EXAMPLE
I take the time from the NOW() function and format it with TIME_FORMAT
SELECT TIME_FORMAT(NOW(), "%r") AS Tiempo;
With an output like this:
Tiempo
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