How to combine date and time from different MySQL columns to compare to a full DateTime?

Question

Column d is DATE, column t is time, column v is, for example, INT. Let's say I need all the values recorded after 15:00 of 01 Feb 2012 and on. If I write

SELECT * FROM `mytable` WHERE `d` > '2012-02-01' AND `t` > '15:00'

all the records made before 15:00 at any date are going to be excluded from the result set (as well as all made at 2012-02-01) while I want to see them. It seems it would be easy if there were a single DATETIME column, but there are separate columns for date and time instead in the case of mine.

The best I can see now is something like

SELECT * FROM `mytable` WHERE `d` >= '2012-02-02' OR (`d` = '2012-02-01' AND `t` > '15:00')

Any better ideas? Maybe there is a function for this in MySQL? Isn't there something like

SELECT * FROM `mytable` WHERE DateTime(`d`, `t`) > '2012-02-01 15:00'

possible?

Solution 1

You can use the mysql CONCAT() function to add the two columns together into one, and then compare them like this:

SELECT * FROM `mytable` WHERE CONCAT(`d`,' ',`t`) > '2012-02-01 15:00'

Solution 2

The TIMESTAMP(expr1,expr2) function is explicitly for combining date and time values:

With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

This resulting usage is just what you predicted:

SELECT * FROM `mytable` WHERE TIMESTAMP(`d`, `t`) > '2012-02-01 15:00'

Solution 3

Here's a clean version that doesn't require string operations or conversion to to UTC timestamps across time zones.

 DATE_ADD(date, INTERVAL time HOUR_SECOND)

Comments

[Ivan]

Is this really meant to work ok with date-times (meaning no significant performance loss and no unexpected behaviour)?

[Brian Glaz]

I'm not sure.. you can run explain on it, and see how it performs. Of you course you could always mass update your table to use one date-time column instead of the two separate ones.

[Brian Glaz]

if you really want a date, you can use this instead dev.mysql.com/doc/refman/5.5/en/…

[Ivan]

By the way, it doesn't work this way. A column alias ("timestamp" here) can't be used in WHERE clause - we have to use HAVING instead. And not date_format(t,'%h) but date_format(t,'%H'). But thank you anyway - the query works about 200 times faster than the query I've used as my first example. Though, @brian-glaz solution is even faster.

[Visruth]

@Brian Glaz The CONCAT(d,' ',t) should be converted to TIMESTAMP(CONCAT(d,' ',t)). Or, it could be simply TIMESTAMP(d, t).

[Sam San]

should be TIMESTAMP(CONCAT(d,' ',t)) > TIMESTAMP('2012-02-01 15:00')

[Maurice]

This is great, but you need to convert the part after > with TIMESTAMP() too.