CODEWITHSUNDEEP
mysql
user1213167
user1213167

Reputation:

Mysql Function to find total no of seconds between two dates

Say i've a from date as 17/10/2012 and to date as 18/10/2012.How will i find total no of seconds that is available ?

Update I do not want to select a row which has exceed to date ?

Thanks in advance!

Upvotes: 1

Views: 447

Answers (4)

Amrita
Amrita

Reputation: 611

 select timestampdiff(second,'2012-10-16','2012-10-18');

output will be 172800

Upvotes: 0

metalfight - user868766
metalfight - user868766

Reputation: 2750

Here is the working demo.

http://sqlfiddle.com/#!2/d41d8/2869

SELECT UNIX_TIMESTAMP('2012-10-19') - UNIX_TIMESTAMP('2012-10-18') as differece_seconds;

"If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC."

You can simply use it with date coulmn. Please check : http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Upvotes: 2

Sirko
Sirko

Reputation: 74046

Use UNIX_TIMESTAMP() to get the seconds since epoch for a specific date. Then just subtract both values.

So assuming the values (in some DATE or DATETIME) are stored in col1 and col2 respectively, use can use something like this:

SELECT UNIX_TIMESTAMP( col1 ) - UNIX_TIMESTAMP( col2 ) AS diff FROM yourTable;

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270617

Assuming your columns are proper DATE/DATETIME columns and not the date strings in your question, subtract the values of UNIX_TIMESTAMP()

SELECT UNIX_TIMESTAMP(date1_column) - UNIX_TIMESTAMP(date2_column) AS difference_seconds

If you have stored dates in the string format you posted above (which is a bad idea), you will need STR_TO_DATE() to first parse them into proper MySQL dates.

SELECT UNIX_TIMSTAMP(STR_TO_DATE('17/10/2012','%d/%m/%%Y')) - UNIX_TIMSTAMP(STR_TO_DATE('18/10/2012','%d/%m/%%Y')) AS difference_seconds

Upvotes: 0

Related Questions