This message was deleted.

Hi Kanthraj, You may use timestamp_parse to convert to any timestamp.

timestamp_parse (string expr, [pattern, [timezone]]) parses a string into a timestamp using a given Joda DateTimeFormat pattern. If the pattern is not provided, this parses time strings in either ISO8601 or SQL format. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as nulls.

Hi, i tried to use that, but its not working for me. SELECT timestamp_parse('2023-04-04 100000', 'yyyy-MM-dd HHmmss', 'UTC') always give me error: druid error: Unknown exception (org.apache.calcite.tools.ValidationException): org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 75: No match found for function signature timestamp_parse(<CHARACTER>, <CHARACTER>, <CHARACTER>)

I tried from one of the druid datasource as below and it is working for

TIME_FORMAT.

SELECT TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss', 'UTC') FROM rle

https://druid.apache.org/docs/latest/misc/math-expr.html#time-functions

Hi am still facing error here: when record gets insert into the druid the time comes in EST time and since it wont have any zone info Druid is treating it as UTC and getting inserted SELECT TIME_FORMAT(__time,'yyyy-MM-dd HHmmss', 'UTC') FROM "XXX" WHERE eventName = 'ACTIVE' ORDER BY __time DESC LIMIT 1 __time stored in druid is in UTC(BUT actual time value is in EST as it wont have the zone info druid assuming it as UTC) when i execute the above query it gives the EST time only not utc.. below query gives the SELECT Current_timestamp as currentTime, TIME_FORMAT(__time,'yyyy-MM-dd HHmmss', 'UTC') as lastruntime from "lockers-stage-lockers-mco" WHERE eventName = 'HEARTBEAT' ORDER BY __time DESC LIMIT 1 screenshot now_time is in

Druid will store the timestamp in UTC only, So while ingesting the data try to convert from EST to UTC and then insert into druid table.