MySQL's Greatest Date and Time Functions

It provides a simple way to use string representations of dates in your SQL when the representation differs to MySQL's internal format. Say for instance, you have a date such as 13th Oct 70, you can use it directly in a SQL statement thanks to this function: STR_TO_DATE('13th Oct 70', '%D %b %y'). Note that the format string accepts the exact same codes as DATE_FORMAT() above.

This function is available since MySQL v4.1.1.

UNIX_TIMESTAMP(date)

This function will give you the UNIX timestamp (ie seconds since the start of 1970) for the specified date column. If you prefer to use PHP's date formating, you can then just feed the resulting value directly into the PHP date() function, avoiding the need to call the PHP strtotime() function first. As date/time columns in MySQL are always formatted the same (Japanese style), it is probably more efficient to do it this way rather than with strtotime(), which can handle dates formatted in just about any way.

I would imagine though that the MySQL DATE_FORMAT(date,format) function, discussed above, is best of all, as it can avoid the intermediate step of converting to a timestamp entirely.

FROM_UNIXTIME(integer timestamp)

Returns a MySQL formatted DATETIME value corresponding to the Unix timestamp passed in (see UNIX_TIMESTAMP() above).

EXTRACT(unit FROM date)

Returns the specified part of the specified date. The units are the same as for DATE_ADD/DATE_SUB eg EXTRACT(YEAR FROM '2002-10-13')

FROM_DAYS(N), TO_DAYS(date)

These two convert back and forth between a date format and an integer representing the number of days since year zero. They don't work for dates preceding the Gregorian calendar (1582).

Some Useful Applications

The following examples refer to a column named 'timestamp', which may be DATETIME or TIMESTAMP.

1)Select Rows Dated Today

SELECT * FROM table WHERE DATE(timestamp) = DATE(NOW())

Here, the DATE() function is simply getting the date part of each DATETIME value.

2)Select Rows From This week

SELECT * FROM table WHERE DATE(timestamp) >= DATE(SUBDATE(NOW(), WEEKDAY(NOW())))

The above will return all rows which have a timestamp column set to a day in the current week. eg if today is a Thursday, it will return rows from Monday to Thursday in the current week. Note the use of >=. If we use DAYOFWEEK() instead of WEEKDAY(), we'd need to replace this with > in order to get rows from Monday on, rather than Sunday.

3) Select Items Scheduled in 15 Minutes

Say you have a table of event listings, scheduled to start on the hour or at half past, and you want to run a script as a cronjob to alert people attending the event XX minutes in advance, you could use a query like the following:

SELECT * FROM events WHERE date=CURRENT_DATE AND (EXTRACT(MINUTE FROM NOW())<30 AND EXTRACT(HOUR FROM startTime)=EXTRACT(HOUR FROM NOW()) AND EXTRACT(MINUTE FROM startTime)=30 OR EXTRACT(MINUTE FROM NOW())>=30 AND EXTRACT(HOUR FROM startTime)=EXTRACT(HOUR FROM NOW())+1 AND EXTRACT(MINUTE FROM startTime)=0)

You would just need to run the query XX number of minutes before the hour/half-hour.

4) Select Number of Years Between Dates

In these examples, I use now() as well as a datetime column in order to find the age of an item, but you could just as well use two datetime columns to find the difference between the two columns.

If you only need a mostly-accurate indication of age, you can get the number of months between two columns like this:

SELECT (YEAR(now()) - YEAR(datetimecol)) * 12 + (MONTH(now()) - MONTH(datetimecol)) AS age FROM table

The above example works because the (MONTH(now()) - MONTH(datetimecol)) part will be negative if the current month precedes the month of the datetime column (and thus result in a subtraction). Dividing by 12 in your script ie intval($monthsold/12), where $monthsold is the number of months returned by the above SQL, would give a mostly accurate age in years. I say mostly accurate because it doesn't consider days - if the datetime column is set to the same month that it is now, it will count that as an additional year regardless of the day of the month.

A better way, which would be suitable for accurately giving someone's age from their birthday for instance, uses the MySQL IF() function, like this:

SELECT IF (MONTH(now()) < MONTH(datetimecol) OR (MONTH(now()) = MONTH(datetimecol) AND DAYOFMONTH(now()) < DAYOFMONTH(datetimecol)), YEAR(now()) - YEAR(datetimecol) - 1, YEAR(now()) - YEAR(datetimecol)) AS ageinyears FROM table

5)Select Rows From This month

SELECT * FROM table WHERE DATE(timestamp) > DATE( SUBDATE( NOW(), DAYOFMONTH(NOW()) ) )

This will return rows where the timestamp column contains a date from the current month.

6)Select Rows From Last week

SELECT * FROM table WHERE DATE(timestamp) BETWEEN DATE(SUBDATE(NOW(), 7 + WEEKDAY(NOW()))) AND DATE(SUBDATE(NOW(), WEEKDAY(NOW()) - 1))

This will return rows where the timestamp column contains a date from the previous week, Monday to Sunday.

Note that BETWEEN is inclusive.

7)Select Rows From Last month

SELECT * FROM table WHERE DATE(timestamp) BETWEEN DATE( DATE_SUB(SUBDATE( NOW(), DAYOFMONTH(NOW()) - 1 ), INTERVAL 1 MONTH) ) AND DATE( SUBDATE( NOW(), DAYOFMONTH(NOW()) ) )

This will return rows where the timestamp column contains a date from the previous month.

8)Select Rows From the last 3 minutes

The TIMEDIFF() function can help us here.

1 2 3