Display data every 30 minutes interval

sam0204

unread,

Nov 23, 2009, 12:49:37 AM11/23/09

to

Hi all,

I have a database which is similiar to

id Date Time
1 2009-08-31 16:00:21
2 2009-08-31 16:00:51
3 2009-08-31 16:01:21
4 2009-08-31 16:01:51
5 2009-08-31 16:02:21
6 2009-08-31 16:02:51
7 2009-08-31 16:03:21
8 2009-08-31 16:03:51
. .
. .
. .
. .
x 2009-08-31 16:30:21 and so on

What sql statement should I write in order to display records that are every
30 minutes?
Thanks for the help!

Regards,
sam

Balaji

unread,

Nov 23, 2009, 1:44:00 AM11/23/09

to

Hi Sam,

You query may be some thing like this...

select * from table
where datetimecol >= convert(varchar(30),dateadd(mi,-30,GETDATE()),108)

Regards, Balaji

"sam0204" <u56410@uwe> wrote in message news:9f87af27dfbf3@uwe...

mark

unread,

Nov 23, 2009, 2:38:34 AM11/23/09

to

why is there a conversion to varchar(30) ? wouldn't that then compare a datetime column type to a varchar?

frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Display-data-every-30-minutes-interval

sam0204

unread,

Nov 23, 2009, 2:50:20 AM11/23/09

to

hi all, thanks for the replies

i have another question though

id Date Time Energy
1 2009-08-31 16:00:21 21
2 2009-08-31 16:00:51 23
3 2009-08-31 16:01:21 30
4 2009-08-31 16:01:51 28
5 2009-08-31 16:02:21 28

. . .
. . .

x 2009-08-31 16:30:21 33

. . .
. . .

x 2009-08-31 17:00:21 34

is it possible to get data like

at time 16:00:21 the energy is 21
and time 16:30:21, energy 33
time 17:00:21 energy 34

only need those data that are 30 min interval on that day.

thanks (:

Balaji

unread,

Nov 23, 2009, 4:37:12 AM11/23/09

to

Hi Sam,

Sorry I am didn't understand your question. Do you need all the records in
the 30 mins duration are only the records in 30th min.

Regards,Balaji

"sam0204" <u56410@uwe> wrote in message news:9f88bccf5bf4b@uwe...

> hi all, thanks for the replies
>
> i have another question though
>
> id Date Time Energy
> 1 2009-08-31 16:00:21 21
> 2 2009-08-31 16:00:51 23
> 3 2009-08-31 16:01:21 30
> 4 2009-08-31 16:01:51 28
> 5 2009-08-31 16:02:21 28
> . .
> . .

> x 2009-08-31 16:30:21 33
> . .
> . .

sam0204

unread,

Nov 23, 2009, 4:59:32 AM11/23/09

to

oo.. I only need the start, 30 min, 60 min, 90min and so on for that single
day which is 2009-08-31 in my example..

sorry for the misleading

Balaji wrote:
>Hi Sam,
>
>Sorry I am didn't understand your question. Do you need all the records in
>the 30 mins duration are only the records in 30th min.
>
>Regards,Balaji
>

>> hi all, thanks for the replies
>>

>[quoted text clipped - 22 lines]
>>
>> thanks (:

mark

unread,

Nov 23, 2009, 6:19:38 AM11/23/09

to

i think you can write this:

where datepart(minute,getutcdate()) in (0, 30)

frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Display-data-every-30-minutes-interval

Uri Dimant

unread,

Nov 23, 2009, 7:18:19 AM11/23/09

to

SELECT

DATEADD(minute, DATEDIFF(minute, '19000101', dt) /30 * 30.0,

'19000101') AS Interval

FROM tbl

"sam0204" <u56410@uwe> wrote in message news:9f89dda66b4fd@uwe...