Waht will kafka index service treat the message timezone with millis timestamp

I’m in timezone ‘Asia/Shanghai (UTC+8)’, my druid run in UTC env ‘-Duser.timezone=UTC’.

I run an kafka index service task, ingest with the following message

{“timestamp”:1512792000000,“page”:“page_1”}

{“timestamp”:1512792000000,“page”:“page_2”}

{“timestamp”:1512792000000,“page”:“page_3”}

{“timestamp”:1512792000000,“page”:“page_4”}

{“timestamp”:1512792000000,“page”:“page_5”}

1512792000000 = 2017-12-09 12:00:00

When I query druid with the following sql, it returns the correct result count=5

{

“query” : “SELECT count(*) FROM tz_test where __time < TIMESTAMP ‘2017-12-09 13:00:00’”,

"context" : {

  "sqlTimeZone" : "**Asia/Shanghai**"

}

}

Note that, in my query, sqlTimeZone is Asia/Shanghai, condition is __time < TIMESTAMP ‘2017-12-09 13:00:00’

My question is that: what will kafka index service task treat the timezone with timestamp 1512792000000.

If druid task treat timestamp 1512792000000(2017-12-09 12:00:00) as UTC timezone(UTC+0),

when convert this timestamp to time of Asia/Shanghai,we will get ‘2017-12-09 20:00:00’(add 8 hours).

If the timezone convert above is correct, my query suppose to return no result(count=0), but i got count=5.

Please help me out with this confusion, any reply will be appreciated.

Your timestamps are being parsed as unix epoch times. A unix time is defined as:

Unix time (also known as POSIX time or epoch time) is a system for describing a point in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, minus the number of leap seconds that have taken place since then.

If converted to Gregorian calendar with UTC timezone, your timestamp is roughly: 2017-12-09 04:00:00 UTC. This is the timestamp associated with your metric rows in druid. When converted to Asia/Shanghai (UTC+8), the Gregorian representation is 2017-12-09 12:00:00 UTC+0800. Thus you get 5 rows as 12:00 < 13:00.

Hope this helps your understanding.

Kyle

Thanks for your reply.
Yes, I understand timestamp 1512792000000 is a point in time.

My confusion is that, as my druid run in ‘-Duser.timezone=UTC’, what will druid convert this timestamp to actual time.

As I mentioned above, if druid treat it as UTC+0 time, then my query suppose to get no result.

It would be very nice if you could explain the question in futher detail.

在 2017年12月12日星期二 UTC+8上午3:10:28,Kyle Boyle写道:

I think you might be complicating this more than you need to. What I described is literally what is happening… Every timestamp in the scenario, both the epoch time in the events and the query parameter, has zone information associated with it (either implicitly (unix epoch) or explicitly (query)). Druid doesn’t have to “infer” anything about the timestamps.

OK,Thanks Kyle.

在 2017年12月12日星期二 UTC+8下午9:56:02,Kyle Boyle写道: