calculation query

Hi all,
I need your help of building the following query.
I have the table "CountersTbl".
The table's fields are.
Date\Time Counter1 Counter2
The tables holds counters in different dates ant time.e.g:
19/11/04 06:00 am 10 20
19/11/04 15:00 pm 15 25
19/11/04 11:00 pm 35 90
...

In the above table we have the counters in three shifts in one day. Each day I hae the same shifts readings.

My task is:
I want to build a query that calculate the difference between the shifts counters in a givven day.
So, the output of the query of the 19/11/04 day is:

From 06:00 am - 15:00 pm 5 5
From 15:00 pm - 11:00 pm 20 65

I you please help me to build the code of this query.
Best regards...select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
, three.Counter1
-six.Counter1 as Counter1Diff
, three.Counter2
-six.Counter2 as Counter2Diff
from CountersTbl as three
inner
join CountersTbl as six
on date(three.datetimecol)
= date(six.datetimecol)
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
union all
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
, eleven.Counter1
-three.Counter1 as Counter1Diff
, eleven.Counter2
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
inner
join CountersTbl as three
on date(eleven.datetimecol)
= date(three.datetimecol)
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
order
by ShiftDate
, Shifthere datetimecol is the name of your "Date\Time" column

also, please note, date and time represent whatever functions are available in your particular database system for extracting the date only and time only portions of the datetime values

i was going to write them using the standard sql EXTRACT function but the "standard sql" book which i own is pretty crappy and does not give enough decent examples for me to know how to extract dates and times

and anyway, most common database systems don't support EXTRACT, they have their own proprietary date functions