CODEWITHSUNDEEP
mysqldatedatetime
chiperortiz
chiperortiz

Reputation: 4991

MySQL substract two datetimes and returns in nanoseconds

I have 2 datetime columns in a MySQL table.

I want to subtract it and return the results and a nanoseconds precision

This is my code so far.

select end,start,end-start from job where id=1;

I have the results like this

'2014-04-02 12:30:00', '2014-04-02 10:30:00', 20000.000000

Just for curiosity how MySQL subtract directly, how 20000.000000 came from.

I am not good handling dates. Which is the best approach?

Upvotes: 1

Views: 1042

Answers (1)

Manwal
Manwal

Reputation: 23836

unix_timestamp: If you want to convert a UNIX TIMESTAMP into seconds since '1970-01-01'

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. 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. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.

Consider this :

SELECT unix_timestamp(now()) - unix_timestamp('2007-03-19 09:50:00')

You want this:

SELECT unix_timestamp(column1) - unix_timestamp(column2) from tableName

See it in action

SELECT start_time, 
       end_time, 
       ( Unix_timestamp(start_time) - Unix_timestamp(end_time) ) * 1000000000 AS 
       NanoSeconds 
FROM   job

Output:

start_time      |       end_time           |    NanoSeconds
April, 02 2014 12:30:00 April, 02 2014 10:30:00 7200000000000

As we can format this datetime column value also with DATE_FORMAT(). TO_SECONDS() is available beginning with MySQL 5.5.0.

MYSQL - datetime to seconds also gives some lightness on another solution of the output you want.

Upvotes: 3

Related Questions