CODEWITHSUNDEEP
mysqldatedatetime
Ilya Cherevkov
Ilya Cherevkov

Reputation: 1803

MySQL TIMEDIFF(DATE, DATETIME)

I have two columns in MySQL database.

One is in DATE format like 2014-01-26, another one is in DATETIME format: 2014-01-25 17:19:07.

I need to apply TIMEDIFF(2014-01-26, 2014-01-25 17:19:07) function, but it requires both variables are in DATETIME format. How can I convert 2014-01-26 to 2014-01-26 00:00:00?

Upvotes: 0

Views: 647

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You can always cast a Date to a datetime

select timediff(cast(<yourDateColumn> as Datetime), <yourDatetimeColumn>)

But I'm not even really sure that you need to cast (depending on your mysql version), I may misunderstand the doc, but we can read

Prior to MySQL 5.1.18, when DATE values are compared with DATETIME values, the time portion of the DATETIME value is ignored, or the comparison could be performed as a string compare. Starting from MySQL 5.1.18, a DATE value is coerced to the DATETIME type by adding the time portion as '00:00:00'. To mimic the old behavior, use the CAST() function to cause the comparison operands to be treated as previously. For example:

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use date_format()

mysql> select 
TIMEDIFF(date_format('2014-01-26','%Y-%m-%d %H:%i:%s'), '2014-01-25 17:19:07') 
as diff;
+----------+
| diff     |
+----------+
| 06:40:53 |
+----------+
1 row in set (0.00 sec)


mysql> select date_format('2014-01-26','%Y-%m-%d %H:%i:%s') as date;
+---------------------+
| date                |
+---------------------+
| 2014-01-26 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions