How to extract datetime from JSON

Data Service
1

Hi,

I am trying to build a generated column which extracts lastSessionStart (a timestamp formatted in ISO format) from JSON column sessionAnalytics.

I have tried the following without success : STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(sessionAnalytics, "$.lastSessionStart")), '%Y-%m-%dT%T.000Z')

The error message I get when creating the generated column is :
Incorrect datetime value: '2022-12-16T17:46:58.948Z' for function str_to_date

Could you point me in the right direction as to the correct syntax ? Thanks !

Side question : is it possible to create this value in a view, so as not to overload the table itself ?

2

Could you please provide a sample JSON document which the column’s expression will be working with?

3

Hi Mark,

Sure :

{
  "lastSessionStart": "2022-12-16T17:46:58.948Z"
}
4

This should do the trick:

STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(testColumn, "$.lastSessionStart")), '%Y-%m-%dT%H:%i:%s.%fZ')
5

That worked, thanks @mark-piller . I wasn’t far, but thanks for expliciting the time syntax.

Now, I am wondering if that formula can be used to define a column inside a view - rather than within the table itself. I haven’t found how.

6

You cannot define generated columns in Views (yet).

7

OK, thanks @mark-piller