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.

CodePudding user response：

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.