Date minus date

PHP
1

I have two fields in a database

datetime - 0000-00-00 00:00:00

datetime - 0000-00-00 00:00:00

I would like to subtract the second date from the first date to get a result such as

23 h 15 m

Any ideas on how this could be accomplished?

2

So you want to find out what the difference is between the two dates and time entered in to the database?

3

Correct

4 
SELECT SEC_TO_TIME(
        UNIX_TIMESTAMP(later_date) -
        UNIX_TIMESTAMP(earlier_date)
                  ) AS diff
  FROM daTable
5

Thanks so much r937! worked like charm.

Does that statement also work for pulling the current time used when the query is ran? Is there a syntax used to pull in the current time?

6

use CURRENT_TIMESTAMP

:slight_smile:

7

MySQL has a couple hundred inbuilt functions on its own. Learning to use them is very important because, usually, the MySQL function can perform a task faster than it’s PHP equivalent. Also it does this before the query result arrives in memory so if your script has a tight memory budget this can become important.

8

Thanks Rudy & Michael.

One last thing:

The output of that is:

-05:30:16

Is it possible to change that to

5h 30m 15s

9

do that with your programming language

sql should be used for retrievals (and some calculations), but is not the best place to do output formatting

10

I feel this is situational and a programmer should know how to do both. I will agree that it certainly isn’t as easy to do output formatting in MySQL. There are also MVC concerns - but these can be mitigated if the model doesn’t try to change it’s output by view mode. This way the view has the responsibility of conversion if it desires to do such.

I believe DATE_FORMAT() can make the conversion, but I’m not sure.

11

So something like this?

DATE_FORMAT(date,%h %i %s)

12

More like this:


echo date_format($date, 'H i s');

The ‘H’ is the hour the twenty four hour format, if you prefer just showing the hour without the leading zero ‘05’ the use ‘h’.

The ‘i’ is for the minutes with leading zeros and the ‘s’ is for the seconds with leading zeros.

More info here at php.net.

13

DATE_FORMAT(date,%h %i %s)

I think its MySQL DATE_FORMAT function which can be used in query, not php.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

14

Unfortunately, I’m not able to get it to work for either. I got errors when I tried to pull the information using both PHP and MYSQL.

Here is the table I’m working with:

CREATE TABLE IF NOT EXISTS dates (
id int(10) NOT NULL auto_increment,
date datetime NOT NULL default ‘0000-00-00 00:00:00’,
lastreply datetime NOT NULL,
PRIMARY KEY (id),
KEY tid_c (tid,c(64)),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

I need to pull data as follows:

CURRENT_TIME - date = 23h 15m
CURRENT_TIME - date = 1d 4h 27m
CURRENT_TIME - lastreply = 23h 15m
CURRENT_TIME - lastreply = 1d 4h 27m

15

could you please elaborate on that a bit?

for the first one, you want to retrieve rows where the date (sad name for a column, btw) column value is ~within~ the last 23 hours and 15 minutes, or … ?

16

It does not need to be within 23 hours etc. It just needs to pull the difference.

For instance if an entry is added into the database it starts a “Stop Watch”.

So if it was inserted into the database one minute ago I would need to show 1m.

I assume this would be completed by pulling in the current time and subtracting that from the entry. The problem is the formatting.

17

in that case i would advise doing the difference calculation in the SQL in seconds (by subtracting two values of UNIX_TIMESTAMP as shown earlier) and then do the formatting in php

sorry i can’t help you with the php

18

If you want it the PHP way, try mktime function.
http://php.net/manual/en/function.mktime.php

OR
http://php.net/manual/en/ref.datetime.php

19

I’m still a bit confused on the formatting.

Here is what Rudy so kindly suggested earlier which pulls the results I need.

SELECT SEC_TO_TIME(
UNIX_TIMESTAMP(later_date) -
UNIX_TIMESTAMP(earlier_date)
) AS diff
FROM daTable

That outputs a format such as:

-110:58:43

This gives me the exact information I need. (It is saying there is a different of 110 hours 58 minutes and 43 seconds in this example).

But I need a way to format that output to say:

4d 6hs 58m

I tried to run the query that Rudy provided which in his example outputted that as ‘diff’

So then I tried to run the query in php to say:

echo DATE_FORMAT(dif,%h %i %s);

However, PHP didn’t recognize the dataformat using echo and gave an error.

20

Anyone have any ideas?