convert date time to time with the query record processor

MarioFRS — Thu, 15 Sep 2022 12:30:16 GMT

Hey everyone,

i get a csv file as record from an api call and don't need all columns and want to convert the datetime columns to a time format.

For example from 15.09.2022 00:15:00 to 00:15

This is my csv record

Interval start	Interval stop	Camera serial number	Counter name	Pedestrians coming in	Pedestrians going out
15.09.2022 00:00:00	15.09.2022 00:15:00	00000011	11234	0	0

This is my setting in the Query record Processor

Query

SELECT #{TodayAxisV2Param} as "date", "Interval start" as "start time", -- Expected FORMAT(HH:mm) "Interval stop" as "end time", -- Expected FORMAT(HH:mm) "Pedestrians going out" as "out" FROM FLOWFILE

I expect this result for the time fields

date	start time	end time	out
20220915	00:00	00:15	0

Does anyone have an idea how I can do this with the Query Record Processor?

Re: convert date time to time with the query record processor

MarioFRS — Fri, 16 Sep 2022 14:02:25 GMT

I have found a solution.
I use in the Query Record Processor the sql function substring and it worked as expected for my case

SELECT SUBSTRING("Interval start", 12, 5) as "start_time" FROM FLOWFILE

store_id	date	start_time	end time	out
1005	20220915	00:00	00:15	0

question convert date time to time with the query record processor in Support Questions

convert date time to time with the query record processor

Re: convert date time to time with the query record processor