rookie_coder rookie_coder - 4 months ago 8
SQL Question

How to get data at the end of the hour of the day

Need some help how to get depth_md_msl from the last end_datetime of the day.

For example this date:

2014-10-15 last depth_md_msl is 41.98 or

2014-10-16 last depth_md_msl of the day is 109.98


when i use the below query it return nothing and its completely empty

SELECT depth_md_msl
FROM fa
WHERE fa.start_datetime >= CURDATE() - INTERVAL 1 DAY



end_datetime depth_md_msl
------------------- --------------

2014-10-15 08:00:00 41.98
2014-10-15 08:00:00 (NULL)
2014-10-15 10:00:00 (NULL)
2014-10-15 10:00:00 41.98
2014-10-15 10:30:00 41.98
2014-10-15 10:30:00 (NULL)
2014-10-15 12:00:00 41.98
2014-10-15 12:00:00 (NULL)
2014-10-15 17:00:00 41.98
2014-10-15 17:00:00 (NULL)
2014-10-15 23:00:00 41.98
2014-10-15 23:45:00 41.98
2014-10-15 23:59:59 41.98
2014-10-16 00:30:00 41.98
2014-10-16 03:15:00 41.98
2014-10-16 12:00:00 106.98
2014-10-16 12:30:00 106.98
2014-10-16 14:00:00 109.98
2014-10-16 15:00:00 109.98
2014-10-16 19:00:00 109.98
2014-10-16 23:59:59 109.98
2014-10-17 06:00:00 109.98
2014-10-17 08:00:00 109.98
2014-10-17 15:30:00 109.98
2014-10-17 19:30:00 109.98
2014-10-17 23:59:59 109.98

Answer

I think that you need to join the table to itself.

 SELECT A.end_datetime, A.depth_md_msl FROM fact_activity AS A
    JOIN (
         SELECT DATE(end_datetime) d, MAX(TIME(end_datetime)) t FROM fact_activity 
        GROUP BY 1
    ) B ON
     DATE(A.end_datetime) = B.d AND
     TIME(A.end_datetime) = B.t