RE: MySql Query help

["Paul Smith" <ukha@xxxxxxxxxxxxx>]

Thanks Guys,

Little bit of tweaking and it does just what I need.


Many Thanks

Paul


-----Original Message-----
From: ukha_d@xxxxxxx [mailto:ukha_d@xxxxxxx] On Behalf
Of Martyn Wendon
Sent: 06 January 2006 09:20
To: ukha_d@xxxxxxx
Subject: Re: [ukha_d] MySql Query help

Hi Paul,

I'd do something like:

select sum(if(date_format(TIMESTAMPCOLUMN, '%T') > 00:30:00 and
date_format(TIMESTAMPCOLUMN, '%T') < 07:30:00,NUMBEROFFLASHES,0)) as
CHEAPRATEFLASHES, sum(if(date_format(TIMESTAMPCOLUMN, '%T') > 07:30:01
and
date_format(TIMESTAMPCOLUMN, '%T') < 00:29:59,NUMBEROFFLASHES,0)) as
NORMALRATEFLASHES from TABLEMETERFLASHES where TIMESTAMPCOLUMN <
date_format(now(), '%Y%m%d000000') and TIMESTAMPCOLUMN >=
date_format(now() - interval 1 day, '%Y%m%d000001');

This should select a sum of the column NUMBEROFFLASHES that occurred
between
00:30:00 and 07:30:00 as CHEAPRATEFLASHES and a sum of the column
NUMBEROFFLASHES that occurred between 07:30:01 and 00:29:59 as
NORMALRATEFLASHES, for the previous day.  It assumes that
TIMESTAMPCOLUMN is
of mysql type TIMESTAMP (i.e. like 20060106081601).

Note I've not tested it though and I've not had a coffee yet so my
brain's
in neutral still :)

Cheers,

Martyn

----- Original Message -----
From: "Paul Smith" <ukha@xxxxxxx>
To: <ukha_d@xxxxxxx>
Sent: Wednesday, January 04, 2006 4:27 PM
Subject: [ukha_d] MySql Query help


> Hi Guys,
>
>
>
>            I've been dumping meter readings into a simple mysql
> database. Basically every minute the number of flashes the meter makes
> is recorded, along with the date-time of the entry.
>
>
>
> A while back I had some help getting out hourly and daily totals which
I
> use to create my live charts. However it dawned on me last night that
if
> I know the time at which the cheap rate switches in I then already
have
> the data in the date base.
>
>
>
> So what I am looking to do is sum up the minute readings between 00:30
> and 07:30 and call that Cheap and then the rest as Normal. In the past
I
> have only had to sum up one total which makes life very easy to do
hours
> or days or month or years, but with this added requirement I am
getting
> very confused.
>
>
>
> Any one care to point me in the right direction.
>
>
>
>
>
> Regards,
>
>
>
>
>
> Paul
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>