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:

SELECT *
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:
SELECT *
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:

SELECT *
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:

SELECT *
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.

Cta

From MySQL query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and MySQL