RE: Now mysql Was {Sql help, for electronic meter reading}

["aashram" <groups@xxxxxxxxxxx>]

Paul try this - you would use HOUR as your type

EXTRACT(type FROM date)

The EXTRACT() function uses the same kinds of interval type specifiers
as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather
than performing date arithmetic.

Examples

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');
-> 20102
mysql> SELECT EXTRACT(MICROSECOND
->                FROM '2003-01-02 10:30:00.00123');
-> 123


-----Original Message-----
From: Paul Smith [mailto:ukha@xxxxxxx]
Sent: 29 March 2005 20:15
To: ukha_d@xxxxxxx
Subject: [ukha_d] Now mysql Was {Sql help, for electronic meter reading}


Hi Guys, a big thank you for all the responses so far.


It would seem that I may have missed out some key information


I have installed mysql 4.1.10a on my server.

I have a simple vb app which every minute dumps the following into a
table (electric) in a database (meter-readings)

In the table the are 3 columns

Count	Reading	Date

So VB dumps 20 and 2005-03-29 20:00:00 into the table, count is just an
incremental number


Using the mysql query browser and

SELECT Sum(reading) AS Reading FROM `electric` where Date between
'2005-03-29 12:00:00' and '2005-03-29 13:00:00'

Returns a sum of all the reading between 12 and 1 o'clock.

What I was trying to then achieve was as Doogie spotted, a table with
the sum for each hour.

Such that I get

Hour	Total
0	20
1	19
2	19
.
.
.
23	40


So Graham, Doogie and Neil

Came up with things like this

SELECT date part(hh,Date) AS HourOfDay, Sum(reading) AS Reading FROM
`electric` where Date between '2005-03-29 00:00:00' and '2005-03-29
23:59:59'
group by datepart(hh,Date)

Select datepart(hour,Date) as ReadingHour, sum(reading) as Reading FROM
'electric' where Date between '2005-03-29' and '2005-03-30'
GROUP BY Datepart(Hour,Date)

SELECT sum(reading) AS Reading FROM electric where DATE >= DATEADD(dd,
DATEDIFF(dd,0,getdate()), 0) group by reading


Which all look good and I can see where you are coming from. However
when I put these into the query browser I get errors.

So I've been goggling for mysql and datepart and dateadd and have drawn
a blank. Is it that these commands haven't been implemented.

If needs be I'll bin mysql and install MS sql 2000 if that's helps.


Many thanks for all your help












Paul Smith



Tel:-  0871 425 1236



This email and any attachments may be confidential and/or privileged.
Everything is intended for use of the addressee only. If you receive
this message in error then you must not print it or pass it on to anyone
else or use the information it contains.  Please inform Phoenix
Technology UK  of the error by email or by telephoning (+44)(0)871 425
1236.  Please then delete all copies from your system.
If you are not the intended recipient then you must not use the
information in the message or attachments or allow anyone else to do so.
Phoenix Technology UK reserves the right to copy and intercept all email
and other data sent over its networks. Phoenix Technology UK cannot
guarantee that this message has reached you complete and/or virus free
and advises you to carry out appropriate virus checks.  The Company does
not accept any liability for any statements made which are clearly the
sender's own and not made on behalf of the Company.