Using current_time system parameter in sql node of Mosaic decisions

Question

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?

Answer 1

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.