One of my ex-students asked for an example of converting a DATETIME column into a DATE data type in MySQL. He’d tried a few approaches and hadn’t been successful. It’s best to use the DATE function to convert a DATETIME to a DATE in MySQL.
- Create a sample
TIMECLOCK table.
DROP TABLE IF EXISTS timeclock;
CREATE TABLE TIMECLOCK
( timeclock_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, timein datetime
, timeout datetime); |
DROP TABLE IF EXISTS timeclock;
CREATE TABLE TIMECLOCK
( timeclock_id int unsigned AUTO_INCREMENT PRIMARY KEY
, timein datetime
, timeout datetime);
- Insert two rows with values in the
TIMEIN column.
-- Insert two rows with automatic numbering and only a "time in" value.
INSERT INTO timeclock
( timein )
VALUES
(NOW()),(NOW()); |
-- Insert two rows with automatic numbering and only a "time in" value.
INSERT INTO timeclock
( timein )
VALUES
(NOW()),(NOW());
- Update the previously inserted rows with values in the
TIMEOUT column. The first UPDATE statement inserts a UTC date into the DATETIME column. That date is a time stamp of of the next day at 12:00 A.M. in the morning. The second UPDATE statement updates the TIMEOUT column with a current time stamp plus 4 hours, thirty-two minutes, and thirty-three seconds. Then, the code segment queries the results.
-- Update with tomorrow's future date at 12:00 A.M..
UPDATE timeclock
SET timeout = ADDDATE(DATE(NOW()), INTERVAL 1 DAY)
WHERE timeclock_id = 1;
-- Update with a timestamp 4 hours, thirty-two minutes, and thirty-three seconds in the future.
UPDATE timeclock
SET timeout = ADDTIME(NOW(), '4:32:33')
WHERE timeclock_id = 2;
-- Query the value sets.
SELECT timein, timeout FROM timeclock; |
-- Update with tomorrow's future date at 12:00 A.M..
UPDATE timeclock
SET timeout = ADDDATE(DATE(NOW()), INTERVAL 1 DAY)
WHERE timeclock_id = 1;
-- Update with a timestamp 4 hours, thirty-two minutes, and thirty-three seconds in the future.
UPDATE timeclock
SET timeout = ADDTIME(NOW(), '4:32:33')
WHERE timeclock_id = 2;
-- Query the value sets.
SELECT timein, timeout FROM timeclock;
This returns:
+---------------------+---------------------+
| timein | timeout |
+---------------------+---------------------+
| 2010-06-18 16:16:08 | 2010-06-19 00:00:00 |
| 2010-06-18 16:16:08 | 2010-06-18 20:48:42 |
+---------------------+---------------------+
2 rows in set (0.00 sec) |
+---------------------+---------------------+
| timein | timeout |
+---------------------+---------------------+
| 2010-06-18 16:16:08 | 2010-06-19 00:00:00 |
| 2010-06-18 16:16:08 | 2010-06-18 20:48:42 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
- Query the differences of the timestamps as dates and times. The
DATE function lets you convert a DATETIME into a DATE data type. Then, the DATEDIFF calculates the difference and returns an integer result (the interval of days). You calculate the time difference by using the TIMEDIFF function.
-- Query the difference in intervals of days.
SELECT DATE(timeout) AS dateout
, DATE(timein) AS datein
, DATEDIFF(DATE(timeout),DATE(timein))
FROM timeclock;
-- Query the difference in intervals of time.
SELECT timeout
, timein
, TIMEDIFF(timeout,timein)
FROM timeclock; |
-- Query the difference in intervals of days.
SELECT DATE(timeout) AS dateout
, DATE(timein) AS datein
, DATEDIFF(DATE(timeout),DATE(timein))
FROM timeclock;
-- Query the difference in intervals of time.
SELECT timeout
, timein
, TIMEDIFF(timeout,timein)
FROM timeclock;
These return:
+------------+------------+--------------------------------------+
| dateout | datein | DATEDIFF(DATE(timeout),DATE(timein)) |
+------------+------------+--------------------------------------+
| 2010-06-19 | 2010-06-18 | 1 |
| 2010-06-18 | 2010-06-18 | 0 |
+------------+------------+--------------------------------------+
2 rows in set (0.00 sec)
+---------------------+---------------------+--------------------------+
| timeout | timein | TIMEDIFF(timeout,timein) |
+---------------------+---------------------+--------------------------+
| 2010-06-19 00:00:00 | 2010-06-18 16:16:08 | 07:43:52 |
| 2010-06-18 20:48:42 | 2010-06-18 16:16:08 | 04:32:34 |
+---------------------+---------------------+--------------------------+
2 rows in set (0.00 sec) |
+------------+------------+--------------------------------------+
| dateout | datein | DATEDIFF(DATE(timeout),DATE(timein)) |
+------------+------------+--------------------------------------+
| 2010-06-19 | 2010-06-18 | 1 |
| 2010-06-18 | 2010-06-18 | 0 |
+------------+------------+--------------------------------------+
2 rows in set (0.00 sec)
+---------------------+---------------------+--------------------------+
| timeout | timein | TIMEDIFF(timeout,timein) |
+---------------------+---------------------+--------------------------+
| 2010-06-19 00:00:00 | 2010-06-18 16:16:08 | 07:43:52 |
| 2010-06-18 20:48:42 | 2010-06-18 16:16:08 | 04:32:34 |
+---------------------+---------------------+--------------------------+
2 rows in set (0.00 sec)
You can also use:
SELECT CAST(timein AS DATE)
, CAST(timeout AS DATE)
FROM timeclock; |
SELECT CAST(timein AS DATE)
, CAST(timeout AS DATE)
FROM timeclock;
Hope this helps the one who asked how and anybody else who runs across it.
Subscribe via RSS