MYSQL - datetime to seconds
If by "convert to seconds", you mean "convert to an UNIX Timestamp" (i.e. number of seconds since 1970-01-01
), then you can use the UNIX_TIMESTAMP
function :
select UNIX_TIMESTAMP(your_datetime_field)
from your_table
where ...
And, for the sake of completness, to convert from an Unix Timestamp to a datetime, you can use the FROM_UNIXTIME
function.
If you want to have the difference between two DATETIME values, use TIMESTAMPDIFF
:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Returns
datetime_expr2 – datetime_expr1
, wheredatetime_expr1
anddatetime_expr2
are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of theTIMESTAMPADD()
function.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885
unit
can also be HOUR
which is what you asked for in one of the comments.
The unit argument can be any of the following:
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
The level of usefulness of some of the other options will of course be determined by the granularity of the data. For instance, "MICROSECOND" will only have limited use if you are not storing microseconds in your DATETIME values.