How to get records after a certain time using SQL datetime field

Question

If I have a datetime field, how do I get just records created later than a certain time, ignoring the date altogether?

It's a logging table, it tells when people are connecting and doing something in our application. I want to find out how often people are on later than 5pm.

(Sorry - it is SQL Server. But this could be useful for other people for other databases)

Answer 1

The best thing I can think would be: don't use a DateTime field; well, you could use a lot of DATEADD/DATEPART etc, but it will be slow if you have a lot of data, as it can't really use an index here. Your DB may offer a suitable type natively - such as the TIME type in SQL Server 2008 - but you could just as easily store the time offset in minutes (for example).

Answer 2

What database system are you using? Date/time functions vary widely.

For Oracle, you could say

SELECT * FROM TABLE 
  WHERE TO_CHAR(THE_DATE, 'HH24:MI:SS') BETWEEN '17:00:00' AND '23:59:59';

Also, you probably need to roll-over into the next day and also select times between midnight and, say, 6am.

Answer 3

For SQL Server:

select * from myTable where datepart(hh, myDateField) > 17

See http://msdn.microsoft.com/en-us/library/aa258265(SQL.80).aspx.

Answer 4

Ok, I've got it.

Select myfield1, myfield2, mydatefield from mytable WHERE DATENAME(hour, mydatefield) > 17)

This will get me records with a mydatefield with a time later than 5pm.

Answer 5

Another Oracle method for simple situations:

select ...
from   ...
where  EXTRACT(HOUR FROM my_date) >= 17
/

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions050.htm#SQLRF00639

Tricky for some questions though, like all records with the time between 15:03:21 and 15:25:45. I'd also use the TO_CHAR method there.

Answer 6

In MySQL, this would be

where time(datetimefield) > '17:00:00'

Answer 7

In Informix, assuming that you use a DATETIME YEAR TO SECOND field to hold the full date, you'd write:

WHERE EXTEND(dt_column, HOUR TO SECOND) > DATETIME(17:00:00) HOUR TO SECOND

'EXTEND' can indeed contract the set of fields (as well as extend it, as the name suggests).

As Thilo noted, this is an area of extreme variability between DBMS (and Informix is certainly one of the variant ones).