Search code examples
mosaic-decisions

Using current_time system parameter in sql node of Mosaic decisions

In the custom SQL node of mosaic-decisions, I used the system parameter “current-time” . However, in the output, it is coming in as “Text” type – instead of “Date” type. What should be done to get it resolved as date in the output?

Solution

  • When you use any parameters in custom SQL node, input reader SQL, writer node pre/post SQL etc. (in short any SQL option), Mosaic Decisions replaces the parameter value inside the SQL as is.

    So the below SQL:

    SELECT COL1,
   '$(currentTime)' as T1
FROM TABLE1..

    will be converted as:

    SELECT COL1,
   '2020-06-17 21:44:55' as T1
FROM TABLE1..

    before it is run on Spark. If you want to get this column as date/timestamp, you will have to cast it explicitly.

    However, also note that, if you are using these parameters in any other nodes (say for example, custom-select node), that will appear as date type only.