Grouping the time (AM and PM)

[Shahith]

I have a column in which there is the date and time together and it is mixed among AM and PM, how can we group them into AM and PM separately?

Any help is greatly appreciated!

[ajaykakkar93 · Accepted solution]

Hi,

This data is not cleaned & not a date-time please follow the below steps

• Get the data format for date-time ( DD-MMM-YY hh:mm tt)
• apply they format in backend 

data:
load *,
	Timestamp(Timestamp#(data,'DD-MMM-YY hh:mm:ss TT'),'DD/MM/YYYY h:mm:ss[.fff] TT') as DateTime;
load * inline [
data,ID
10-Oct-23 10:22:12 AM,1
10-Oct-23 10:22:12 AM,2
10-Oct-23 10:21:55 AM,3
];

exit Script;

Explanation:

Timestamp(Timestamp#(<date here>,'<format to convert from>'),'<format to convert to>')
<date here> = 10-Oct-23 10:22:12 AM
<format to convert from> = DD-MMM-YY hh:mm:ss TT
<format to convert to> = DD/MMM/YY hh:mm:ss TT

[Shahith]

Awesome! It worked, Thank you so much!!

And I'm facing another issue with the same, I have another column with this "DateTime" named "OutCount",

basically I need to create a line or bar chart and show the count that comes in every 30minute interval and 1 hour interval, i tried different ways and i guess it didn't go well, could you please help me with that as well?

[ajaykakkar93]

Ok, you need to create a grouping by Hr, simple solution will be just use Line chart as it is a time series it will have that hr based view