MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Timestamp to Date?

with 3 comments

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.

  1. 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);
  1. 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());
  1. 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;

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)
  1. 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;

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)

You can also use:

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.

Written by maclochlainn

June 18th, 2010 at 5:48 pm

Posted in MySQL,sql

3 Responses to 'MySQL Timestamp to Date?'

Subscribe to comments with RSS or TrackBack to 'MySQL Timestamp to Date?'.

  1. Excellent tutorial! My next question is, how would I do a query on my database from PHP (using a prepared statement) where I could do a CAST from a TIMESTAMP to a Date object? I.e, I have a TIMESTAMP of, “2010-06-18 10:29:46″. How could I format it to say: “Jun 18, 2010″ using the prepared statement in PHP?
    Thanks!

    Trevor D

    22 Jun 10 at 12:23 pm

  2. thank u very much………superb tutorial

    vijay

    24 Nov 10 at 4:25 am

  3. Trevor, I missed this earlier. Are you asking about the strtotime() function?

    maclochlainn

    24 Nov 10 at 11:01 am

Leave a Reply