MySQL interval math with subqueries

I had a query I needed to modify that required getting all the rows more recent than a certain number of hours ago. The “number of hours” was stored in a row in another table. I thought, oh boy, this is going to be fun, because how will I retrieve that and include it in the comparison. Then I remember I’m working with MySQL and often with MySQL SQL if you think it it will work it does (of course, often in MySQL what you think should work never returns a result-set)

So, I decided to just throw a subquery into the interval part of the clause and see what happens – it worked! Wow, this is a nice capability and I’ll have to see if it would work in Oracle too, but I’m doubting that.

Here is a simple example of how you can do this:

mysql> select * from jed order by 1;
+---------------------+
| col1 |
+---------------------+
| 2013-12-14 06:58:52 |
| 2013-12-15 06:58:56 |
| 2013-12-16 06:59:00 |
| 2013-12-17 06:59:09 |
| 2013-12-18 06:59:12 |
| 2013-12-19 06:59:17 |
| 2013-12-20 06:59:28 |
| 2013-12-21 06:59:32 |
+---------------------+
8 rows in set (0.00 sec)
mysql> select * from params;
+-------+
| value |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-12-19 07:10:28 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from jed where col1 > (current_timestamp - interval (select value from params) day) order by 1;
+---------------------+
| col1 |
+---------------------+
| 2013-12-18 06:59:12 |
| 2013-12-19 06:59:17 |
| 2013-12-20 06:59:28 |
| 2013-12-21 06:59:32 |
+---------------------+
4 rows in set (0.00 sec)

Very cool!

OK, so I tried in Oracle and here is what I got, if you figure something out that is contained within SQL (not PL/SQL) let me know:

SQL> select * from ora where col1 > systimestamp - interval (select value from params) day order by 1;
select * from ora where col1 > systimestamp - interval (select value from params) day order by 1
 *
ERROR at line 1:
ORA-00936: missing expression
SQL> select * from ora where col1 > systimestamp - interval '(select value from params)' day order by 1;
select * from ora where col1 > systimestamp - interval '(select value from params)' day order by 1
 *
ERROR at line 1:
ORA-01867: the interval is invalid
SQL> select ora.*
 2 from ora, (select value from params) p
 3 where ora.col1 > systimestamp - interval p.value day
 4 order by 1;
where ora.col1 > systimestamp - interval p.value day
 *
ERROR at line 3:
ORA-00933: SQL command not properly ended

 

Advertisements

One response to “MySQL interval math with subqueries

  1. Pingback: MySQL interval math with subqueries | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s