Get timestamp when using xbar

[dorwalprateek]

Hi,

I am very new to Q, and I assume this would be a very basic question.

I wanted to do a binned average of 10 seconds on my data. I am able to get the average using xbar, but loose the timestamp information.

select Timestamp, value from t
Timestamp               value
---------------------------------------
2018.07.27T04:04:27.000 294.6376
2018.07.27T04:04:28.000 293.1288
2018.07.27T04:04:29.000 291.4341
2018.07.27T04:04:30.000 291.3495
2018.07.27T04:04:31.000 298.0625
2018.07.27T04:04:32.000 301.1601
2018.07.27T04:04:33.000 302.7948
2018.07.27T04:04:34.000 300.6647
2018.07.27T04:04:35.000 298.3461
2018.07.27T04:04:36.000 295.5218
2018.07.27T04:04:37.000 293.989
2018.07.27T04:04:38.000 294.3222
2018.07.27T04:04:39.000 298.7421
2018.07.27T04:04:40.000 307.4469
2018.07.27T04:04:41.000 308.5945
2018.07.27T04:04:42.000 307.2776
2018.07.27T04:04:43.000 303.6355
2018.07.27T04:04:44.000 300.2119
2018.07.27T04:04:45.000 296.2123
2018.07.27T04:04:46.000 294.3902




q)select avg value by 10 xbar Timestamp.second from t
second  | value
--------| ---------------
04:04:20| 293.0668
04:04:30| 297.4953
04:04:40| 301.5107
04:04:50| 301.927
04:05:00| 304.0808
04:05:10| 304.7161
04:05:20| 308.8453
04:05:30| 313.3515
04:05:40| 311.9577
04:05:50| 313.8456
04:06:00| 315.125
04:06:10| 316.5125
04:06:20| 323.6668
04:07:10| 338.6279
04:07:20| 335.7249
04:07:30| 332.4239
04:07:40| 332.1968
04:07:50| 338.2006
04:08:00| 331.0576
04:08:10| 333.6152

I want to get the grouped column as 2018.07.27T04:04:20 instead of 04:04:20

Thanks in advance!

Thanks - D

[Jamie_O__Mahony]

operate on the timestamp directly instead of casting it:

select avg price by 10*1000000000 xbar time from t

Also note that value is a kdb+ keyword and can cause issues as a column name, better to rename it. 

[charlie]

+1 on not using keywords 🙂

Prateek appears to be using datetime rather than timestamp type.

select avg val by  (1%8640) xbar time from t
watch out for grouping/keys on types which use an underlying floating point (e.g. datetime).

Timestamp type is represented by a 64bit int, achieve the same with
select avg val by 0D00:00:10 xbar timestamp from t

[dorwalprateek]

Thanks Charles and Jamie. 

 (1%8640) works for me. I guess its datetime. datatype is "Z".

Thanks for the suggestion of not using keywords 

Thanks - D