Ibanez1408 Ibanez1408 - 15 days ago 4
MySQL Question

Get time in and time out from two rows in a single column in MySql

What I want to achieve is to get the time-in and time-out of each eno per date so what I have in mind is to get the minimum time for the time-in and the maximum time for the time-out for each date. But I don't know how to do it since, group by the date is not working.

eno dateAndTime Door
636 2013-01-14 11:52:34.373 7 4
637 2013-01-14 11:52:41.780 8 4
638 2013-01-14 12:17:12.300 7 4
639 2013-01-14 12:17:23.207 8 4
640 2013-01-14 12:39:55.803 7 4
641 2013-01-14 12:55:58.003 7 4
642 2013-01-14 12:56:47.360 8 4
643 2013-01-14 13:43:20.453 8 4
644 2013-01-14 16:46:19.143 7 38
645 2013-01-14 16:47:05.397 8 38
646 2013-01-14 17:16:30.253 7 38
647 2013-01-14 17:17:27.193 8 38
648 2013-01-14 17:17:47.897 7 38
649 2013-01-14 17:23:04.640 8 38
650 2013-01-14 17:24:40.250 7 38
651 2013-01-14 17:25:23.360 8 38
654 2013-01-14 21:57:56.403 7 5
656 2013-01-14 21:59:04.280 8 5
659 2013-01-15 04:13:38.783 7 38
660 2013-01-15 04:14:03.283 8 38
661 2013-01-15 02:59:26.977 7 3
662 2013-01-15 03:00:35.840 8 3
663 2013-01-15 03:08:01.693 7 39
664 2013-01-15 03:08:26.023 8 39
665 2013-01-15 03:12:49.013 7 39
666 2013-01-15 03:13:46.000 8 39
668 2013-01-14 23:36:23.880 8 40
669 2013-01-14 23:40:01.337 7 40
672 2013-01-14 23:40:39.150 8 40
677 2013-01-15 04:35:12.970 7 3
678 2013-01-15 02:36:52.707 7 5
697 2013-01-15 02:53:16.810 8 5
698 2013-01-15 02:54:07.107 7 5
700 2013-01-15 04:58:22.440 8 3
703 2013-01-15 05:22:49.480 7 3
705 2013-01-15 05:44:43.357 8 3
706 2013-01-15 05:47:26.487 7 3
707 2013-01-15 05:47:42.597 8 3
708 2013-01-15 03:54:52.840 8 5
709 2013-01-15 03:55:38.920 7 5
711 2013-01-15 05:55:44.000 8 5
712 2013-01-15 03:56:37.687 7 5
714 2013-01-15 03:56:43.920 8 5
715 2013-01-15 04:10:54.820 7 5
717 2013-01-15 04:11:08.443 8 5
718 2013-01-15 04:12:32.947 7 5
720 2013-01-15 04:12:38.743 8 5
721 2013-01-15 04:15:47.843 7 5
723 2013-01-15 04:15:54.640 8 5
724 2013-01-15 06:20:12.350 7 3
725 2013-01-15 06:20:38.193 8 3
726 2013-01-15 04:20:46.910 7 5
728 2013-01-15 04:20:49.897 8 5
729 2013-01-15 06:15:14.463 7 5
731 2013-01-15 10:32:13.537 7 38
732 2013-01-15 11:10:14.990 7 38
733 2013-01-15 11:10:25.913 8 38
738 2013-01-15 11:24:29.533 8 38
739 2013-01-15 11:26:28.020 7 38
741 2013-01-15 11:36:24.213 8 38
742 2013-01-15 11:43:19.457 7 38
743 2013-01-15 11:44:49.100 8 38
761 2013-01-15 13:09:51.080 7 4
762 2013-01-15 13:10:04.207 8 4
763 2013-01-15 13:10:21.690 7 3
764 2013-01-15 13:12:08.617 8 3
765 2013-01-15 13:13:25.163 7 3
766 2013-01-15 13:14:49.400 8 3
767 2013-01-15 13:17:20.230 7 3
768 2013-01-15 13:17:43.467 8 3
771 2013-01-15 14:58:25.400 7 3
774 2013-01-15 13:18:37.350 8 5
779 2013-01-15 20:28:59.957 7 5

Answer

You can use the MySql DATE function to filter the time and get the date:

http://www.w3resource.com/mysql/date-and-time-functions/mysql-date-function.php

Identify the DATE() column with a new name.. so something like this:

SELECT *, DATE(dateAndTime) as 'justDate' FROM yourTable GROUP BY `justDate`

This syntax might be off a bit, I have not written any SQL for a while.

Comments