MySQL convert timestamp and time string to DateTime format

Question

In my table have two columns, one as timestamp to save the date and one as time string to save the time with period.
Eg:

I want to combine them into one column as DateTime format in the result then order by desc on that column.

Here is the example: http://sqlfiddle.com/#!9/25eb21/4
The column name 'Datetime' expected is Datetime or timestamps type, so I can sort correctly on it.

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Dec 19 '20 at 10:30
What should the results you look like? Please edit your question to add this important piece of information. — GMB, Dec 19 '20 at 10:30

score 1 · Answer 1 · answered Dec 19 '20 at 12:00

Let me assume that you want to add the duration that is stored as a string in column apptTime to timestamp in column apptDate.

A typical approach uses str_to_date() to turn the string to a datetime, then converts the time portion to seconds using time_to_sec(), which we can then add to the timestamp using date artihmetics.

So

select t.*
    apptdate 
        + interval time_to_sec(str_to_date(appttime, '%h:%i %p')) second 
        as newapptdate
from mytable

score 1 · Accepted Answer · answered Dec 19 '20 at 13:31

1

You do not need to convert the values to integers to add them. MySQL has built-in functions for this purpose:

SELECT *,
       addtime(apptDate, str_to_date(apptTime, '%h:%i %p')) as datetime
FROM appt
ORDER BY Datetime DESC;

If apptTime is just a time value (which it should be), then you obviously do not need to convert from a string. I would usuggest fixing the data model.

answered Dec 19 '20 at 13:31

Gordon Linoff

1,242,037
58
646
786

In my MySQL server, the STR_TO_DATE() function always return Null – dangquang1020 Dec 21 '20 at 10:26
@dangquang1020 . . . It certainly seems to work on the data you provided: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7e6df70e0276c92f50571a3c2e18a4b9. – Gordon Linoff Dec 21 '20 at 13:39

score 0 · Answer 3 · answered Dec 19 '20 at 10:35

select addtime(appDate, appTime) from ...

Your appDate contains a time, probably because you are applying a timezone. Either convert your two columns to the timezone your data is supposed to be in with convert_tz(), or extract the date part of it with date(appDate) before you add it. It wasn't clear which of the columns was a string, but extract() or str_to_date() is the way to parse a text into a date and/or time.

MySQL convert timestamp and time string to DateTime format

3 Answers3