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:
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 of
I take the time from the
NOW() function and format it with
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