Hello, I wasn't able to find out (googling, reading mysql reference manual) how to get value of DATETIME in seconds in MySQL.
I dont mean to extract seconds from datetime, but to convert it into seconds.
Thanx
+1
A:
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.
Pascal MARTIN
2010-03-13 15:22:37
goodand when I want DIFFERENCE between two datetimes in HOURS, should I use something like? : select (unix_timestamp('2010-01-02 18:00:00') - unix_timestamp('2010-01-01 16:00:00'))/60/60;
Mike
2010-03-13 15:27:32
I suppose the TIMESTAMPDIFF function should do the trick : it allows one to specify the desired unit (see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff )
Pascal MARTIN
2010-03-13 15:34:27
thank you, Felix already suggested using timesteampdiff, thats what I wanted
Mike
2010-03-13 15:36:45
You're welcome :-) *(Well, it was hard to guess from your question that you wanted to find out the difference between two datetimes ^^ )*
Pascal MARTIN
2010-03-13 15:38:01
Yep I thought I'll make it like (datetime_to_sec(value1) - datetime_to_sec(value2))*60*24 - but thats rubbish as I see now :))
Mike
2010-03-13 15:43:01
+1
A:
If you want to have the difference between to DATETIME values, use TIMESTAMPDIFF:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Returns
datetime_expr2 – datetime_expr1, wheredatetime_expr1anddatetime_expr2are 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.
Felix Kling
2010-03-13 15:27:33
A:
The function UNIX_TIMESTAMP(datetime) returns the unix time, which happens to be the number of seconds since 1-Jan-1970 0000 UTC. That may be what you need, but not if you're dealing with dates of birth, historical dates, or dates after 2037.
Ollie Jones
2010-03-13 15:28:42