CODEWITHSUNDEEP
mysql
Bruce Callahan
Bruce Callahan

Reputation: 21

How to calculate timedifference in one column for a day with a custom start time

I have a table containing 160,000 entries. Each entry represents one measurement value at a given time spanning 12 Years; MySQL version is 5.6

id  date_obs        value_obs
+++++++++++++++++++++++++++++++++
1,  2006-01-22T20:21:46    +30),
2,  2006-01-22T22:34:35    +30),
3,  2006-01-22T23:42:37    +30),
4,  2006-01-23T00:42:15    +30),
5,  2006-01-23T00:57:23    +30),
6,  2006-01-23T01:17:13    +30), 
7,  2006-01-23T02:22:32    +30),
8,  2006-01-24T19:26:35    +30),
9,  2006-01-24T20:01:29    +30),
10, 2006-01-24T21:12:02    +10),
11, 2006-01-24T21:12:17    +10),
12, 2006-01-24T21:12:31    +10),
13, 2006-01-28T20:10:43    +30),
14, 2006-01-28T20:22:39    +30),
15, 2006-01-28T21:33:09    +10),
16, 2006-01-28T22:17:17    +10);
++++++++++++++++++++++++++++++++

I am searching for the number of seconds between the first and last measurement on each day, grouped by month.

But: one day lasts from 12:00 to 12:00 and NOT vom 00:00 to 24:00

Like id 1-7 belong to one day, id 8-12 to the next day and id 13-16 to the following day.

Expected result is:

2006-01-22   = 21646
2006-01-24   = 6356
2006-01-28   = 7594

My query is:

SELECT DATE_FORMAT(`date_obs`, '%Y-%m-%d') as T_DATE,
TIMESTAMPDIFF(SECOND,MIN(`date_obs`),MAX(`date_obs`)) as T_SESSION
FROM t1
GROUP BY LEFT(`date_obs`,10);

Of course the result is not correct: 

 2006-01-22   = 12051
 2006-01-23   = 6017
 2006-01-24   = 6356
 2006-01-28   = 7594

because id's 1-3 and 4-7 are treated as measurements from two separate days. see: http://sqlfiddle.com/#!9/c9e570/2/0

Upvotes: 2

Views: 42

Answers (1)

splash58
splash58

Reputation: 26153

Shift each time of period from noon to next noon into one date by adding 12 hours in group clause 

SELECT 
   DATE(`date_obs`) as T_DATE, 
   TIMESTAMPDIFF(SECOND,MIN(`date_obs`),MAX(`date_obs`)) as T_SESSION
FROM t1
GROUP BY date(`date_obs` + INTERVAL 12 HOUR);

results in

T_DATE      T_SESSION
2006-01-22  21646
2006-01-24  6356
2006-01-28  7594

sqlfiddle

Upvotes: 2

Related Questions