Time Difference Query

[Blackborder]

I'm trying to find a succinct way of writing the query:

SELECT * FROM tablename WHERE datetime1 *IS-MORE-THAN-7-DAYS-OLDER-THAN datetime2;

I can't find anything elegant which works. The best I can do is:

SELECT * FROM tablename WHERE (UNIX_TIMESTAMP(datetime1)-UNIX_TIMESTAMP(datetime2))>604800;

..but I'm not sure how efficient this is, as I am running this query on some pretty big tables (40,000+ rows).

Any comments or suggestions are very welcome.

Thanks very much :)

[nicc777]

I created a little test to experiment. Here goes. The first part is a small test to experiment with several functions. You can use the following SQL to recreate it:

<SQL>
#
# Table structure for table `test`
#

DROP TABLE IF EXISTS test;
CREATE TABLE test (
ind int(11) NOT NULL auto_increment,
datetime1 datetime NOT NULL default '0000-00-00 00:00:00',
datetime2 datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (ind)
) TYPE=MyISAM;

#
# Dumping data for table `test`
#

INSERT INTO test (ind, datetime1, datetime2) VALUES (1, '2000-01-01 01:00:00', '2000-01-10 02:00:00'),
(2, '2000-01-02 01:30:00', '2000-01-10 02:05:00'),
(3, '2000-01-03 01:40:00', '2000-01-05 03:10:00'),
(4, '2000-01-03 01:20:00', '2000-01-11 02:25:00'),
(5, '2000-01-04 01:15:00', '2000-01-11 02:30:00'),
(6, '2000-01-04 01:50:00', '2000-01-12 05:30:00'),
(7, '2000-01-05 01:05:00', '2000-01-12 05:05:00'),
(8, '2000-01-06 01:10:00', '2000-01-08 06:15:00'),
(9, '2000-01-07 01:00:00', '2000-01-14 00:59:50'),
(10, '2000-01-11 01:05:00', '2000-01-18 01:05:00');
</SQL>

There are several ways to do this. Here is some of my results:

mysql> select ( datetime2 - datetime1 ) from test;
+---------------------------+
| ( datetime2 - datetime1 ) |
+---------------------------+
| 9010000 |
| 8007500 |
| 2017000 |
| 8010500 |
| 7011500 |
| 8038000 |
| 7040000 |
| 2050500 |
| 6995950 |
| 7000000 |
+---------------------------+
10 rows in set (0.00 sec)

AND

mysql> select ( unix_timestamp( datetime2 ) - unix_timestamp( datetime1 ) ) as timediff from test;
+----------+
| timediff |
+----------+
| 781200 |
| 693300 |
| 178200 |
| 695100 |
| 609300 |
| 704400 |
| 619200 |
| 191100 |
| 604790 |
| 604800 |
+----------+
10 rows in set (0.00 sec)

AND

mysql> select ( to_days( datetime2 ) - to_days( datetime1 ) ) as timediff from test;
+----------+
| timediff |
+----------+
| 9 |
| 8 |
| 2 |
| 8 |
| 7 |
| 8 |
| 7 |
| 2 |
| 7 |
| 7 |
+----------+
10 rows in set (0.01 sec)

Now, how do we know which query is the best optimized?

I created a perl script and populated the table with 1000000 records. Here is the output, first of the populate script:

$ ./datetimetest.pl
DB Populate took 482.033849954605 seconds

Now let's look at the querie results script:

]$ ./datetimetest2.pl
Query 1 returned 194801 results in 0.524204015731812 seconds
Query 2 returned 199633 results in 5.10596311092377 seconds
Query 2 returned 199633 results in 0.921015024185181 seconds

As you can see, query 1 is definately the fastest.

Here is the three queries I ran in Perl

Q1> SELECT COUNT( ind ) FROM test WHERE ( ( datetime2 - datetime1 ) < 8000001 );

Q2> SELECT COUNT( ind ) FROM test WHERE ( unix_timestamp( datetime2 ) - unix_timestamp( datetime1 ) < 604801 );

Q3> SELECT COUNT( ind ) FROM test WHERE ( to_days( datetime2 ) - to_days( datetime1 ) < 8 );

There is one STRANGE thing you will notice - query 1 returned LESS results then quries 2 and 3.

I still need to figure this out. I think is a question of accuracy. As queries 2 and three seems consistant in the nr of results, it might be better to for query 3, in terms of SPEED and ACCURACY.

Hope that helps.

Also refer to this page in te MySQL help...

Cheers

[Blackborder]

Hey thanks very much. That's a way better reply than I had expected.
:D