Adding one hour on date time, with AM PM in count-CodePudding

I want to get the data between 2 dates, I have a procedure that takes fromDate as input, for example like if I give this date for it 2022/02/02 9:10:36 PM, and I want to add 3 hours to the ToDate, so it should be like that: 2022/02/02 12:10:36 AM

I tried DATE_ADD('2022/02/02 9:10:36 PM', INTERVAL 3 Hour) but it didn't work it gave it PM and it should be AM since its 12 AM after adding 3 hours to 9 pm.

SUMMARY: I want to get data with 3 hour range in procedure 2022/02/02 9:10:36 ((PM)) to 2022/02/02 12:10:36 ((AM)), the procedure take the from date and i want to add three hours with AM PM in count

CodePudding user response：

First you have to convert the string date to an actual datetime type and then add the hours. Use str_to_date() which allows you to tell the conversion process what the string looks like so it can complete the conversion correctly.

Here is a simple demo

SELECT STR_TO_DATE('2022/02/02 9:10:36 PM', '%Y/%m/%d %h:%i:%s %p') original, 
        DATE_ADD( STR_TO_DATE('2022/02/02 9:10:36 PM', '%Y/%m/%d %h:%i:%s %p') , INTERVAL  3 Hour) new_dt;

RESULT

original             new_dt
2022-02-02 21:10:36  2022-02-03 00:10:36

It is best to store dates and times in the appropriate data type in the first place so functions work correctly automatically. If your location requires a specific presentation of the date, do that conversion in the presentaion layer not the storage layer