By saturnino


2018-07-12 00:15:52 8 Comments

I've got 2 datetime cols that represent start of work and end of work: field_dates_value field_dates_value2

I have to SUM day hours of work and night hours of work grouped by month.

The complex thing is that for my business night is starting à 20:00:00 and is ending à 05:59:00. If a man start is work at 18:00 and end à 22:00, the result is that hours before 20:00 are considered as day work and hours after 20:00 are considered as night work. So in in this case 2 hours of day's work and 2:00 of night's work.

In the example below i can SUM timestamps between 2 dates grouped by day and another col:

field_dates_value   2018-07-12 18:00:00   
field_dates_value2  2018-07-13 02:00:00


SELECT SUM( UNIX_TIMESTAMP( field_dates_value2 ) - UNIX_TIMESTAMP( field_dates_value ) ) AS total, bundle, DAY( field_dates_value ) AS day FROM  `bons_field_revision_field_dates` WHERE field_dates_value >  '2018-07-11 00:00:00' GROUP BY bundle, day ORDER BY day, bundle LIMIT 0 , 30

My problem is that i don't know how to get sums form an interval of 2 cols before and after 20:00 .

Any ideas how to achieve this?

thank you very muche for your help

0 comments

Related Questions

Sponsored Content

33 Answered Questions

[SOLVED] How do I get the number of days between two dates in JavaScript?

  • 2009-02-12 19:50:25
  • Michael Haren
  • 372974 View
  • 304 Score
  • 33 Answer
  • Tags:   javascript date

43 Answered Questions

[SOLVED] Calculating the difference between two Java date instances

7 Answered Questions

[SOLVED] Get difference between 2 dates in JavaScript?

  • 2010-07-11 22:19:54
  • chobo2
  • 560441 View
  • 369 Score
  • 7 Answer
  • Tags:   javascript date

15 Answered Questions

[SOLVED] Calculate difference between two dates (number of days)?

  • 2009-10-22 13:47:15
  • leora
  • 883051 View
  • 838 Score
  • 15 Answer
  • Tags:   c# date

32 Answered Questions

[SOLVED] Difference in months between two dates

  • 2011-01-09 11:59:57
  • Rauf
  • 229150 View
  • 265 Score
  • 32 Answer
  • Tags:   c# .net vb.net date

6 Answered Questions

[SOLVED] Number of days between two dates in Joda-Time

  • 2010-09-27 10:21:30
  • pvgoddijn
  • 170837 View
  • 285 Score
  • 6 Answer
  • Tags:   java date jodatime

6 Answered Questions

[SOLVED] Get hours difference between two dates in Moment Js

1 Answered Questions

2 Answered Questions

Sponsored Content