CODEWITHSUNDEEP
mysqlsql
Mehraban
Mehraban

Reputation: 3324

How to compare time and date columns together in mysql?

I have a table about how often an event occurs, and I want to see first and last occurrence of every event. Assume I have three columns, event_id, event_time and event_date. I know queries like this are wrong, and give false result:

SELECT 
    event_id, 
    MIN(event_time), 
    MAX(event_time), 
    MIN(event_date), 
    MAX(event_date)
FROM events_table
WHERE 1
GROUP BY event_id

Is there any way I could do this without merging two columns?

Upvotes: 0

Views: 83

Answers (2)

IgorM
IgorM

Reputation: 1356

You forgot FROM

Here is another approach. Tested on my MySQL

SELECT a1.event_id, 
       Min(Concat(a2.event_date, ' ', a2.event_time)), 
       Max(Concat(a2.event_date, ' ', a2.event_time)) 
FROM   event_log a1 
       INNER JOIN event_log a2 
               ON a2.event_id = a1.event_id 
GROUP  BY a1.event_id

Upvotes: 1

gantners
gantners

Reputation: 471

Please be more precisely regarding column types (date,time,varchar..) and if you would look for occurence of the event by day or ever.

You can just concatenate these two columns and min,max it.

Following assumes event_time like '08:03:21' and event_date('2014-01-30') occurence ever:

SELECT id, event_id, min(concat(event_date,' ',event_time)),max(concat(event_date,' ',event_time))
from t_test
group by event_id

If you would like to check the first,last occurence per day, just group by day and remove the date from concat().

Upvotes: 2

Related Questions