i have interesting case. have set of datetimes , need find minimum each day. database stores them utc need grouped according (1) local timezone , (2) custom "my day starts @ xx:xx" field.
for example, if following dataset:
+----------+---------------------+ | id | start | +----------+---------------------+ | 1 | 2015-04-26 16:00:00 | +----------+---------------------+ | 2 | 2015-04-26 17:00:00 | +----------+---------------------+ | 3 | 2015-04-26 20:00:00 | +----------+---------------------+ | 4 | 2015-04-27 01:00:00 | +----------+---------------------+ | 5 | 2015-04-27 16:25:00 | +----------+---------------------+ | 6 | 2015-04-27 16:35:00 | +----------+---------------------+ | 7 | 2015-04-28 02:00:00 | +----------+---------------------+ and if person querying in new york (utc+4), , says day starts @ "3:30am" (utc+4+3:30), need following results:
+----------+---------------------+ | id | start | +----------+---------------------+ | 2 | 2015-04-26 17:00:00 | +----------+---------------------+ | 6 | 2015-04-27 16:35:00 | +----------+---------------------+ notice times utc time "first" records of day start "at or after" 3:30am time in new york.
currently query looks this:
select * records group date(start); i do have use of php, simple calculating time delta (ex: utc+4+3:30 => +7.5 hours or +450 minutes) , using date_add()?
is correct/best way it?
select * records group date(date_add(start,interval 450 minute));
