How to Query Date and Time in MySQL

MySQL has the following functions to get the current date and time:

SELECT now();  -- date and time
SELECT curdate(); --date
SELECT curtime(); --time in 24-hour format

To find rows between two dates or timestamps:

FROM events
where event_date between '2018-01-01' and '2018-01-31';

-- Can include time by specifying in YYYY-MM-DD hh:mm:ss format:
FROM events
WHERE event_date BETWEEN '2018-01-01 12:00:00' AND '2018-01-01 23:30:00';

To find rows created within the last week:

FROM events
WHERE event_date > date_sub(now(), interval 1 week);

There's also DATE_ADD(). For example, to find events scheduled between one week ago and 3 days from now:

FROM events
WHERE event_date BETWEEN date_sub(now(), interval 1 week) AND date_add(now(), interval 3 day);

You can extract part of a timestamp by applying the corresponding function:

SELECT year(now()); -- or month(), day(), hour(), minute(), second()

To get a day of week from a timestamp, use the DAYOFWEEK() function:

-- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday
SELECT dayofweek('2018-12-12');

-- returns the string day name like Monday, Tuesday, etc
SELECT dayname(now());

To convert a timestamp to a unix timestamp (integer seconds):

-- This will assume time to be 12am
SELECT unix_timestamp('2018-12-09');

-- You can specify an exact timestamp to be converted down to the second
SELECT unix_timestamp('2018-12-09 14:53:21');

-- calling unix_timestamp without a parameter will be like calling it for current timestamp
SELECT unix_timestamp(); -- same as SELECT unix_timestamp(now());

To calculate the difference between two timestamps, convert them to unix timestamps then perform the subtraction:

-- show seconds between delivery and shipping timestamps
SELECT unix_timestamp(delivered_at) - unix_timestamp(shipped_at);
FROM deliveries;

-- convert computed difference to hh:mm:ss format:
SELECT sec_to_time(unix_timestamp(delivered_at) - unix_timestamp(shipped_at))
FROM deliveries;

Note that MySQL also has DATEDIFF() and TIMEDIFF() functions, but they can only used with purely date values or purely time values, respectively.

database icon
Real-time SQL collaboration is here
Get started with PopSQL and MySQL in minutes