Best way to convert 24 HR format into 12 HR format

[Einayyar]

24 hr format to 12.xlsx (350.5 KB)

Hello, is there a way to convert the 24 hr format listed in the attached spreadsheet into a 12 hr format?

[wkhan]

Here is an example I put together to do this. There might be more efficient ways. Your data is a little hard to make sense of so this is only using the first couple of rows. Should be a good starting point.

Convert 24HR to 12 HR – wkhan

Regards,
Wali

[Daniel_Weikert]

You could give this a try

convertdate – daniel_weikert

br

[Einayyar]

Thank you for both of your inputs. However, I am unable to access your workflows as I continue to get this error

Object from URI ‘convertdate – KNIME Hub’ couldn’t be pasted

Any thoughts?

[wkhan]

Here is the direct download link for both https://api.hub.knime.com/repository//Users/wkhan/Public/Convert%2024HR%20to%2012%20HR:data
https://api.hub.knime.com/repository//Users/daniel_weikert/Public/daniel_weikert_convert_date_to_us_format:data

I’ve attached the workflows here as well.Convert 24HR to 12 HR (1).knwf (27.3 KB)
daniel_weikert_convert_date_to_us_format.knwf (397.0 KB)

[Einayyar]

Thank you. I will have to download this on my personal laptop as I am getting a company firewall. I will let you know if these provide the solutions that I need.

[bruno29a]

Hi @Einayyar , the basic logic would be:

If hour is 0, then hour should be 12 AM
If hour is 12, then hour should be 12 PM
If hour is greater than 12, then hour should be (hour - 12) PM
The rest would be hour AM

There are a few ways to implement this. I’ve implemented it in 2 ways.
Method 1: Script via Column Expressions (Everything in 1 node):

Time = split(column("Hour & Minute Ship date"), ":")
if(Time[0] == 0) {
    Time[0] = "12";
    AMPM = " AM";
} else if(Time[0] == 12) {
    AMPM = " PM";
} else if(Time[0] > 12) {
    Time[0] = Time[0] - 12;
    AMPM = " PM";
} else {
    AMPM = " AM";
}
join(Time[0], ":", padLeft(Time[1], 2, "0"), AMPM)

Results:

Method 2: Pure Knime nodes, no scripting.

Workflow looks like this:

Here’s the workflow: Convert 24HR into 12HR format.knwf (323.8 KB)

[takbb]

Hi @Einayyar ,

I may be missing something (it’s been a long week! ) but I think that converting between date or time formats should be relatively “straightforward” using the standard KNIME date&time to String and String to date&time nodes.

The idea is that you convert whatever you have into a Date/Time data type, and then from there you can convert it back into a String in whichever format you want.

Convert clock formats.knwf (304.1 KB)

[wkhan]

Ah nice one @bruno29a I forgot to count for hour ‘0’ as 12 AM.

[Einayyar]

This is great! Thank you @bruno29a !!

[lenexa_jayhawk]

Warning
ALWAYS get the context of your problem before jumping to the solution.

By far the simplest solution, agreed. Heads up, though: The solution requires knowing which time format the original data is in before the String to Date&Time node will convert correctly. Trying to convert to a US-based time will fail (or at least leave missing values where the conversion fails). It wasn’t until I tried (and ultimately saw) the original data is in en-GB format that I could reproduce this simple solution.

[takbb]

Hi @lenexa_jayhawk,

Welcome to the KNIME forum!

Yes you are right. I would imagine that knowing the format of your input data is important before trying to convert it

Whilst the mechanics of the solution can be applied generally, the workflow I posted was obviously specific to the question asked, and sample data, so if the input format were different, the format mask used would potentially have to change. I think that similar would probably be the case whether you use a script/code solution or a time format mask.

I wasn’t quite sure what you meant though by trying to convert to a US-based time. In what way is US time different from the UK 12 hour clock representation? I’d assumed in that regard we were the same. I’ll have a play next time I’ve got KNIME open and I’ll try setting the locale to US…

[lenexa_jayhawk]

Great to be here!

Oddly, when I recreated my solution using the same three nodes I left the Locale as en-US, and the node failed to change the 1:0 rows; it left them missing (?). When I switched to en-GB the node worked. Based on your response I just went back into my workflow and changed back to en-US and voila! It worked. Weird. But, my point is still important for my own work: get the context of the problem fully!

[takbb]

@lenexa_jayhawk Thanks for the additional info. Snippets of information like that will often help somebody in future who experiences similar. That is odd indeed, but (lol) I now also understand the… er… context