Converting String Data Type to date (short) format

[Shaji Narayanan]

Hi All, I need to convert a string field (Eg: 2018-09-20T 17:27:33) to short date format in LiveView Web. I tried separating date, month & year parts using substr and join them using date function. But its not working. I will be really helpful if anyone can suggest any solution for this case.

[Steve Barber]

Hi, I'm going to try to answer your question, but it would have helped if you gave more precise examples of what output format you wanted. You said you want "short date format" but I'm not sure exactly what you mean by that exactly as that term might mean different things in different contexts.

 

When I do a Google search for "short date format," I get a lot of hits for a Microsoft Excel term where a short date format -- at least usingUS date formats -- to use your example input string of '2018-09-20T 17:27:33' would be '9/20/2018' -- so let's use that for now.

 

First, I'll note that this isn't specifically a question about LiveView Web -- the LiveView Web client doesn't expose any functionality for you to do arbitrary data value transformations using its visualizations (though you could write your own custom card visualization and do the data transformation in JavaScript). But I think more specifically, you are asking about whether you can do the string transformation as part of the LiveQL query -- and that's LiveView client type-independent. It'd be same in LiveView Web, LiveView Desktop, lv-client, etc.

 

In a LiveQL query, we can use almost any valid StreamBase expression (including built-in simple functions) in the projection. So all you really need is a StreamBase expression that will convert your input date-time string to the Excel short date format.

 

Your input string there has a T and a space in the middle. Let's assume the extra space character is a typo since having a T character between the date and the time is a pretty common format. Then we can use parse_time() to convert your string into a StreamBase/LiveView timestamp, then we can use format_time() to convert that into an Excel-style short date format string. (You could use get_X() functions to extract each field from the timestamp and then convert all that back into strings and concatenate them, but that is messier.) So here's an example that satisfies your question as I understand it:

 

format_time(parse_time('2018-09-20T17:27:33', "yyyy-MM-dd'T'HH:mm:ss"), 'M/d/yyyy') --> '9/20/2018'Now, it might be that all your input date and time strings aren't exactly in the format of the one you've shown here, and you may have to modify your format strings to match your actual data. But I can only work with what you've told me, so I won't try to extrapolate beyond that.

 

If I've guessed wrong here about what you were trying to ask, please feel free to clarify the question!