There is a manual page on Time and Date functions.
The best way to compare date and time and get minutes back is probably to use one of the following:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)
Note that while TIMEDIFF
works with times, TIMESTAMPDIFF
requires a date component - and the values are switched around for TIMESTAMPDIFF
.
You can return hours with either of these:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)
Note that when using DATEDIFF
, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).
To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:
SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)
This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.
'2013-08-25T17:00:00+00:00'
This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect.
The documentation explains what ALLOW_INVALID_DATES
does:
Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.
In other words, 2013-02-31
would be a permissible date if allow_invalid_dates
is set. This option does not do anything when the date or datetime isn't even in a valid format for MySQL.
The +00:00
is the timezone offset from UTC. In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.
Your workaround would be to remove the STRICT_TRANS_TABLES
from the sql_mode
that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1
-- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
-- current session -- it does not make a server-wide config change
mysql> set @@sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)
-- now MySQL will accept the invalid value, with a warning
mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
-- the value did get inserted, but the time zone information was lost:
mysql> select * from datetimetest;
+----+---------------------+
| id | dt |
+----+---------------------+
| 1 | 2013-08-26 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)
Best Answer
If this gives you an error:
But this doesn't:
You are most likely using a EU-like timezone, in particular, CET (I strongly suggest not to use tz on databases, and just use UTC, it is just easier). EU (and other countries) changed the clocks from 2 -> 3 am that Sunday for summertime adjustment, so it is not possible, if using such a TZ, to insert a '2019-03-31 02:XX:XX ' data, as such data is invalid for that timezone.
Do:
To know which timezone you are using. If it says SYSTEM, check it on the os.
Fix: Do not insert it, that clock time didn't exist, either it happened before 2 or after 3. Or fix your timezone of your database or os to match the one you are trying to insert.
Proper fix: Do not handle timezones on the database (never, ever try to implement then on your own), do it only at presentation time, and use UTC for your application backend. Set your servers also to use UTC.