How does hour(), minute() and second() work with date?

[Former Member]

Dear all,

I just found a confused problem:

I am trying to select rows from a table with conditions of specific hour, minute and second. But HANA returned with some confused results:

(what I really need is the row 499 and 500)

Do I need to change my SQL script ?

Thanks a lot!

Best Regards,

Shu

[former_member210482 · Accepted solution]

Hi Shu,

Why dont you try extracting time from timestamp first and then checking for hour, minute and seconds?  Hope this helps..

Cheers

Safiyu

[Former Member · Accepted solution]

Dear Safiyu,

Thank you for your reply! I tried  extract (hour from(begin_time)), but the result doesn't change and most of the result is not the right one I need(only the second and dayofmonth is resonable).

Is there any other solution or how to extract time out of a timestamp datatype. Thanks a lot!

Cheers

Shu

Result figure:

--------------------------

After more experiments, I found that it will work fine if there is NO function of dayofmonth().

But if I add the condition of dayofmonth(), things will go strange:

Thanks a lot!

[former_member210482 · Accepted solution]

Hi Shu,

Did you try hour(time(begin_time))? It definitely works in sql. Haven't tried in sqlscript.. Let me know if it succeeds..

Regards,

Safiyu

[lbreddemann · Accepted solution]

Hey Shu,

I think the problem here is a misunderstanding of what SECOND() will return.

It does not return an integer, but the second and the fractions!

Let's test this:

create column table id_ts (id int, ts timestamp);

insert into id_ts (
        select ifnull ((select max(id) from id_ts), 0)+1, 
        add_seconds( current_timestamp, to_int(rand ()*1000)) 
        from dummy)
select * from id_ts;

ID	TS
	...
10	2015-02-27 16:40:21.336
11	2015-02-27 16:31:55.487
	...

Let's say record with ID = 10 is what I am after.

select id, ts, hour(ts) , minute(ts), second(ts)
 from id_ts
 where id =10

ID	TS	HOUR(TS)	MINUTE(TS)	SECOND(TS)
10	2015-02-27 16:40:21.336	16	40	21.3360000

Now let's select this record based on hour() and minute()

select id, ts, hour(ts) , minute(ts), second(ts)
 from id_ts
 where 
    (    hour(ts) = 16
     and minute(ts) = 40
    )

ID	TS	HOUR(TS)	MINUTE(TS)	SECOND(TS)
9	2015-02-27 16:40:33.168	16	40	33.1680000
10	2015-02-27 16:40:21.336	16	40	21.3360000

As expected, this is not granular enough.

So let's add the second()

select id, ts, hour(ts) , minute(ts), second(ts)
 from id_ts
 where 
    (    hour(ts) = 16
     and minute(ts) = 40
     and second(ts) =  21
    )

which gives us back nothing.

select id, ts, hour(ts) , minute(ts), second(ts)
 from id_ts
 where 
    (    hour(ts) = 16
     and minute(ts) = 40
     and second(ts) = to_decimal( 21.336) 
    )

ID	TS	HOUR(TS)	MINUTE(TS)	SECOND(TS)
10	2015-02-27 16:40:21.336	16	40	21.3360000

Ok, this works, but is maybe not what you want.

So, let's add a different conversion:

select id, ts, hour(ts) , minute(ts), second(ts)
 from id_ts
 where 
    (    hour(ts) = 16
     and minute(ts) = 40
     and to_integer(second(ts)) = 21 
    )

ID	TS	HOUR(TS)	MINUTE(TS)	SECOND(TS)
10	2015-02-27 16:40:21.336	16	40	21.3360000

There you go, now you know

Happy weekend!

- Lars

[Former Member · Accepted solution]

Dear Safiyu,

Thanks!  I tried it.

It looks like Time is not allowed to be used in sql script.

Could not execute 'select time_2, point_2 from "TRAFFIC"."PAIRs_camera_d19" where hour(time(time_2)) = 10 and ...' in 5 ms 69 µs .

SAP DBTech JDBC: [328] (at 74): invalid name of function or procedure: TIME: line 7 col 7 (at pos 74)

[Former Member · Accepted solution]

Dear Lars,

Thank you very much for your detailed and vivid reply. I tried your example which is very easy to understand.

But as I mentioned, the same problem is when I add the function  dayofmonth() as a condition:

Right without dayofmonth():

Strange with dayofmonth():

Happy weekend:)!

Shu

[lbreddemann · Accepted solution]

I cannot reproduce this:

--> add records with different day

insert into id_ts (


        select  id + 30,
                add_days(ts, 1)
        from id_ts)

select id, ts, hour(ts) , minute(ts), second(ts)
from id_ts
where
    (    hour(ts) = 16
     and minute(ts) = 40
     and dayofmonth (ts)= 28
    )

/*

ID  TS                      HOUR(TS)    MINUTE(TS)  SECOND(TS)

39  2015-02-28 16:40:33.168 16          40          33.1680000

40  2015-02-28 16:40:21.336 16          40          21.3360000

*/

select id, ts, hour(ts) , minute(ts), second(ts)
from id_ts
where
    (    hour(ts) = 16
     and minute(ts) = 40
     and dayofmonth (ts)= 27
    )

/*

ID  TS                      HOUR(TS)    MINUTE(TS)  SECOND(TS)

9   2015-02-27 16:40:33.168 16          40          33.1680000

10  2015-02-27 16:40:21.336 16          40          21.3360000

*/  

This is on Rev. 92.

- Lars

[Former Member · Accepted solution]

Dear Lars,

The sad thing is I still meet the same situation after I add days with your script:

This is on Rev 1.00.091.00.1418659308 (fa/newdb100_rel).

Should I contact our Administrator?

Thanks a lot for your reply!

Best,

Shu

[lbreddemann · Accepted solution]

Now, I'd say you seem to hit a bug that you probably can avoid by using rev. 92.

You may also go on and open a support issue to have this checked by SAP support.

- Lars

[Former Member · Accepted solution]

Thank you Lars!

-Shu

[Former Member]

Thank you Lars!

-Anurag

[former_member210482]

Hi Shu,

Oh that's cool. Thanks for this info...

Cheers

Safiyu