Filtering date time by each hour of each day.

  • Hi!

    i have a table that contains a datetime column.

    That column contains the date and hour of each five minutes.

    sample.

    value name datetime

    27,3346Caudal18-05-2010 9:12:51

    27,9530Caudal18-05-2010 9:17:47

    27,2727Caudal18-05-2010 9:22:49

    28,0767Caudal18-05-2010 9:27:52

    28,0767Caudal18-05-2010 9:32:54

    I need to filter by datetime but get each hour of each day.

    This is what i expect to get;

    value name datetime

    27,3346Caudal18-05-2010 9:12:51

    27,9530Caudal18-05-2010 10:12:47

    27,2727Caudal18-05-2010 11:12:49

    28,0767Caudal18-05-2010 12:12:52

    28,0767Caudal18-05-2010 12:12:54

    Thanks in advance.

  • On what basis would you want to return

    '28,0767Caudal18-05-2010 12:12:52'

    from the record that is stored as

    28,0767Caudal18-05-2010 9:27:52?'

    Without explaining exactly what you're trying to do and why, it seems very random.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • im sorry if i wasnt clear.

    Here are actuals records.

    26,4069Caudal20-05-2010 1:02:12

    26,5306Caudal20-05-2010 1:07:13

    26,8398Caudal20-05-2010 1:12:16

    26,8398Caudal20-05-2010 1:17:18

    26,9017Caudal20-05-2010 1:22:21

    27,1490Caudal20-05-2010 1:27:23

    26,9635Caudal20-05-2010 1:32:30

    27,1490Caudal20-05-2010 1:37:32

    27,0872Caudal20-05-2010 1:42:31

    26,9635Caudal20-05-2010 1:47:33

    26,9017Caudal20-05-2010 1:52:35

    27,0872Caudal20-05-2010 1:57:38

    27,0254Caudal20-05-2010 2:02:41

    26,8398Caudal20-05-2010 2:07:45

    27,0254Caudal20-05-2010 2:12:46

    26,7780Caudal20-05-2010 2:17:52

    26,8398Caudal20-05-2010 2:22:53

    26,5925Caudal20-05-2010 2:32:56

    27,1490Caudal20-05-2010 2:32:57

    26,8398Caudal20-05-2010 2:37:58

    26,3451Caudal20-05-2010 2:43:05

    26,8398Caudal20-05-2010 2:48:07

    26,4069Caudal20-05-2010 2:53:06

    26,5306Caudal20-05-2010 2:58:08

    26,4688Caudal20-05-2010 3:03:10

    I want the records closer to a full hour. This would be the output from the above data.

    26,4069Caudal20-05-2010 1:02:12

    27,0872Caudal20-05-2010 1:57:38

    26,5306Caudal20-05-2010 2:58:08

    Hope that makes it clear.

  • Here is one way: -- Setup Data

    SET DATEFORMAT DMY

    DECLARE @Foo TABLE (Val1 VARCHAR(50), Val2 VARCHAR(50), DateVal DATETIME)

    INSERT @Foo

    SELECT '26,4069', 'Caudal', '20-05-2010 1:02:12'

    UNION ALL SELECT '26,5306', 'Caudal', '20-05-2010 1:07:13'

    UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 1:12:16'

    UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 1:17:18'

    UNION ALL SELECT '26,9017', 'Caudal', '20-05-2010 1:22:21'

    UNION ALL SELECT '27,1490', 'Caudal', '20-05-2010 1:27:23'

    UNION ALL SELECT '26,9635', 'Caudal', '20-05-2010 1:32:30'

    UNION ALL SELECT '27,1490', 'Caudal', '20-05-2010 1:37:32'

    UNION ALL SELECT '27,0872', 'Caudal', '20-05-2010 1:42:31'

    UNION ALL SELECT '26,9635', 'Caudal', '20-05-2010 1:47:33'

    UNION ALL SELECT '26,9017', 'Caudal', '20-05-2010 1:52:35'

    UNION ALL SELECT '27,0872', 'Caudal', '20-05-2010 1:57:38'

    UNION ALL SELECT '27,0254', 'Caudal', '20-05-2010 2:02:41'

    UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:07:45'

    UNION ALL SELECT '27,0254', 'Caudal', '20-05-2010 2:12:46'

    UNION ALL SELECT '26,7780', 'Caudal', '20-05-2010 2:17:52'

    UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:22:53'

    UNION ALL SELECT '26,5925', 'Caudal', '20-05-2010 2:32:56'

    UNION ALL SELECT '27,1490', 'Caudal', '20-05-2010 2:32:57'

    UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:37:58'

    UNION ALL SELECT '26,3451', 'Caudal', '20-05-2010 2:43:05'

    UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:48:07'

    UNION ALL SELECT '26,4069', 'Caudal', '20-05-2010 2:53:06'

    UNION ALL SELECT '26,5306', 'Caudal', '20-05-2010 2:58:08'

    UNION ALL SELECT '26,4688', 'Caudal', '20-05-2010 3:03:10'

    -- Select Rows

    SELECT

    *

    FROM

    (

    SELECT

    *,

    ROW_NUMBER() OVER

    (

    PARTITION BY DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60,0)

    ORDER BY ABS(DATEDIFF(SECOND, DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60,0), DateVal))

    ) AS RowNum

    FROM

    @Foo

    ) AS T

    WHERE

    RowNum = 1

  • Wow, you beat me to it and with 10 less lines of code

    I made one adjustment to your code (in the order by)

    ORDER BY ABS(CAST(DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60, 0) - DateVal AS DECIMAL(18, 17)))

    because a second is an eternity!

    [edit] actually:

    ORDER BY ABS(DATEDIFF(MS, DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60,0), DateVal))

    would be better

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply