Can we change string column to timestamp?

[Laxmikant Pandhare]

Hi All,

Through druid injection job, I loaded data and the data is loaded as String datatype except __time.

There is one another field which is a timestamp column but it got loaded as a string though injection job.

Is there any way to change datatype to timestamp? Shall I do any change in injection job to this solved? Any help will be appreciated!

Thank You,

Laxmikant

[Ben Krug]

Hello -

__time is special, and it's the only column that is stored as "timestamp" data type.  For a secondary timestamp field,

you can either store as a string (eg, '2023-09-12 05:01:02') or as a long, with seconds (or millis, etc) since epoch (1970-01-01).

Either way, you can use functions at query time to get it out in the format you want.

--
You received this message because you are subscribed to the Google Groups "Druid User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/adc3d063-85f4-49f7-a082-aa93ddd57949n%40googlegroups.com.

[Ben Krug]

Should have also sent this link:  https://druid.apache.org/docs/latest/ingestion/schema-design/#secondary-timestamps

[Laxmikant Pandhare]

So, I can do typecast in query like below

SELECT cast(timestamp) from table

But, will this impact the performance of the query in druid? 

[Ben Krug]

I don't have numbers, but on the one hand, if you filter on expressions (eg, "WHERE TIME_FORMAT(..."), I'd expect measurable overhead;

on the other hand, I would expect (hope) that if you just use it for formatting the display output, I'd expect it to be pretty quick.  (You can see

the relevant functions for druid SQL, including TIME_FORMAT, here.)

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/90640d26-3d68-40bc-996d-91d86eb6c69en%40googlegroups.com.

[Laxmikant Pandhare]

thank you for your quick response.