convert date time to time with the query record processor

[#1]

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?

[#2 · Accepted solution]

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