Logical problem

[BubikolRamios]

date time1 time2 time3 ...
01.01.2010 00:00:10 09:00:00 ...
02.01.2010 18:00:00 03:00:00 ...
...



select .. where date between 01.01.2010 and 01.30.2010

That is ok, complication arises when I want to get all dates between 01.01.2010 and 01.30.2010 but all data moved 6 hours backward.
That is +/- 12 hours;

See problem in action, js doh, but there are bugs and I want to do it server side:
http://agrozoo.net/jsp/Tools.jsp?p0=PlantingCalendar&l2=en

How would I go about that ?

[guelphdad]

Your between clause won't work if your dates are in that format. your dates need to be yyyymmdd otherwise they are being sorted as a string and not a date.

[Old Pedant]

Not hard at all. But a bit tedious.

So, I gather you stored your dates and times in *separate* database fields??? Never a good idea. You should have stored the combination date/time in a single field.

Ugh...

Okay, let's say you have DB fields named date and time1 (and date is a keyword, so would have been better not to have used it, but never mind). And you want to find out if date+time1-6 hours is between day1 and day2.


WHERE DATE_SUB( DATE_ADD( `date`, INTERVAL TO_SEC(`time1`) SECOND ) INTERVAL 6 HOUR )
BETWEEN '2010-1-1 0:00:00' AND '2010-1-30 23:59:59'

Does that make sense? We convert your time1 value to seconds and then add that many seconds to your `date` value. Then we subtract 6 hours from that value to arrive at the final date+time that we can then use in the BETWEEN.

[BubikolRamios]

yeah, I ended up with one column only for date. Doh proplems arised for missing dates, i.e. dates for which there are no data. And allso there is luckily group_concat function that does a kind of pivot table. Now it operates np.

[BubikolRamios]

ahh I knew it, it operates OK only on initial state, and if lucky on some other.
Say I have this data, for each day in month a data that means something:

1.1.2010 01:00:00
2.1.2010 01:00:00
3.2.2010 07:00:00
...
1.30.2010 xx:xx:xx

and substract 6 hours

1.1.2010 19:00:00
3.2.2010 01:00:00
...
1.30.2010 xx:xx:xx


see how any data for 2.2.2010 disapeared. as you said tedious ...