Pick 1 record per date without repeating the date

Question

Pick 1 record per date without repeating the date

Navigation

#1 by (0 votes)

-3

I have a box system where every time the box is opened and closed per day is registered in the table, the box is opened and closed several times a day, however I would like to only pick up the first opening of the day and with that list the last 30 days showing only the first opening of each day.

How do I mount a select to work this way? already tried limit, top1 none of that worked for me.

Thank you.

mysql sql

asked by anonymous 13.06.2018 / 16:25

1 answer

Truncate integer value PHP [closed] How do I make a cell a button in a table?

score 0 · Answer 1

First you select the Day (Date without time) and then you use the MIN function on the date and time, grouping by day. Example:

select
    cast(c.data as date) as dia,
    min(c.data) as abertura
from caixas c
group by cast(c.data as date);

Result:

dia         abertura
2017-05-01  2017-05-01T10:41:23Z
2017-05-02  2017-05-02T18:40:28Z
2017-05-03  2017-05-03T18:40:31Z