CODEWITHSUNDEEP
mysqldatetime
Mike
Mike

Reputation: 1217

MYSQL - datetime to seconds

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.

Upvotes: 45

Views: 86270

Answers (7)

Rinoyrix
Rinoyrix

Reputation: 17

I have created my own query for your problem:

SELECT HOUR(`colname`) * 3600 + MINUTE(`colname`) * 60 + SECOND(`colname`)
FROM widgets
WHERE id = 1;
  • Use id = 1 if you have to take a specific row.
  • The output will be in seconds.

Upvotes: 0

Felix Kling
Felix Kling

Reputation: 817198

If you want to have the difference between two DATETIME values, use TIMESTAMPDIFF:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns datetime_expr2 – datetime_expr1, where datetime_expr1 and datetime_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 the TIMESTAMPADD() 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.

Upvotes: 44

Rolf Kransse
Rolf Kransse

Reputation: 71

Use TIME_TO_SEC in previous versions for mysql

SELECT TIME_TO_SEC(time column) FROM table

Upvotes: 7

Biggum
Biggum

Reputation: 372

Starting in mysql 5.5.0 you can use to_seconds()

TO_SECONDS(FIELD_NAME)

FIELD_NAME must be DATETIME type

Upvotes: 2

Shanaka
Shanaka

Reputation: 41

i used in mysql

TO_SECONDS(your date goes here) method to convert date to seconds from year 0

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 4

O. Jones
O. Jones

Reputation: 108839

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.

Upvotes: 3

Pascal MARTIN
Pascal MARTIN

Reputation: 401182

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.

Upvotes: 50

Related Questions