MySQL equivalent of php's strtotime()

SuprMan 0
Viewed 22

Is there a way to convert a string such as "-1 week" or "-5 minutes" into a datetime value in MySQL similar to php's extremely convenient strtotime() function?

I have a table that stores a human-readable time interval (such as "2 minutes") in one column and a datetime in another column.

I would like to select the rows where more than the amount of time specified in interval has elapsed since datetime.

strtotime time datetime mysql
1 Answers

MySQL doesn't have an equivalent of PHP's strtotime() in the sense that there is nothing that will automatically attempt to parse and determine the format of a date string using by assuming multiple formats.

What it does have is STR_TO_DATE(str,format) which requires you specify the format of your date, time or date + time string. It is the equivalent of PHP's date_create_from_format(format, str) function (though the format of the format parameter are different).

Here are some examples given from the MySQL documentation. They show a date being passed along with the format string that lets it know how the date string is to be interpreted:

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');

Alternatively, you can cast a string to a date, time or datetime type, but they require a specific format (YYYY-MM-DD hh:mm:ss.fraction) for it to work:

SELECT CAST("2019-11-21" AS DATE);

If you deviate too far from that format it will make a few assumptions but could produce an incorrect date.

userJQ660
Related