Question about time filtering

Community Support
1

Hi all

I have some data with time in GMT and data at Oct, I need to update the time to AU local time so I do a query like

t2:select date,time:time+11:00:00 from t1

In t1, there are some records at time from GMT 23:00 to 23:59, after the time update, those time become 10:00 to 10:59. However, if I use below query try to find the data I need:

select from t2 where time <= 10:16

It always return empty table even there are records there, what is the reason behind and how can I filter those?

2

Hey Carfield,

Is the time definitely rolling over? When you add a time to a time you get a time datatype but it isn’t limited to 24 hours, eg:

q)t

date       time     sym

2017.04.17 23:00:01 bfc

2017.04.17 23:00:02 dbe

2017.04.17 23:00:04 oen

2017.04.17 23:00:05 eal

q)select date, time+11:00:00 from t

date       time

2017.04.17 34:00:01

2017.04.17 34:00:02

2017.04.17 34:00:04

2017.04.17 34:00:05

2017.04.17 34:00:07

A solution to this is to add the date and time before adding 11 hours. This will factor the date into calculations and allow you to roll the date over, eg:

q)select date, time:“t”$(date+time)+11:00:00 from t

date       time

2017.04.17 10:00:01.000

2017.04.17 10:00:02.000

2017.04.17 10:00:04.000

2017.04.17 10:00:05.000

2017.04.17 10:00:07.000

2017.04.17 10:00:08.000

but notice that the date column is now incorrect since the date will have also rolled over. You could avoid this by simply leaving it as a timestamp column which may also save you some problems in future. If you want to have separate columns though, you can do this:

q)t2:select date:“d”$(date+time)+11:00:00, time:“t”$(date+time)+11:00:00 from t

q)t2

date       time

2017.04.18 10:00:01.000

2017.04.18 10:00:02.000

2017.04.18 10:00:04.000

2017.04.18 10:00:05.000

2017.04.18 10:00:07.000

q)select from t where time<=10:16

date       time

2017.04.18 10:00:06.197

2017.04.18 10:00:06.575

2017.04.18 10:00:08.121

2017.04.18 10:00:09.347

2017.04.18 10:00:14.288

2017.04.18 10:00:16.694

Another point to note is that the datatype is different between some versions of q. For example, q 2.8 converts (date+time) into a datetime. Version 3.0 + convert to a timestamp.

Regards,

Aidan

3

I see, thanks a lot, I just aware that it look rollover but actually not, thanks