-
1. Re: Model Date Type for epoch time
You probably need to read as long, then use the FROM_UNIXTIME function in a view to converting to a date. Since this function returns a string you may have to PARSETIMESTAMP around it. See [1]
select PARSETIMESTAMP(FROM_UNIXTIME(myEpoch), "yyyy/mm/dd hh:mm:ss") from source_table
if above is your view definition, then the view can expose as a date
-
2. Re: Model Date Type for epoch time
so, if I am understanding you correctly, in my view, I should set the field as a long then do a transformation to use PARSETIMESTAMP?
or in my query should I use PARSETIMESTAMP?
-
3. Re: Model Date Type for epoch time
Model in source model table as long. Then in the view model created view with date column using above sql as transformation.
-
4. Re: Model Date Type for epoch time
thank you.
I am getting the following error:
TEIID30070 The function 'FROM_UNIXTIME(A.end_date)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.
-
5. Re: Model Date Type for epoch time
What is the type of the column "A.end_date"? This needs to be long, that represents the seconds. Read the document link I provided above. Post the VDB contents if not sure.
-
6. Re: Model Date Type for epoch time
it's a long that represents milliseconds time.
I think I am getting confused with where in the xml this part should go. I used it in the "selectSql" attribute of the <nested xsi:type="transformation:SqlTransformation"> element
-
7. Re: Model Date Type for epoch time
If you are using Designer, it goes in the View Transformation panel. You should not edit XML file directly when working with Designer.
-
8. Re: Model Date Type for epoch time
thank you for pointing me in the right direction, I was able to get it to work using:
PARSETIMESTAMP(FROM_UNIXTIME(cast((A.end_date / 1000) AS integer)), 'yyyy-mm-dd hh:mm:ss.SSS') AS end_date
apparently FROM_UNIXTIME only supports integer
-
9. Re: Model Date Type for epoch time
I sas in document as Long. You should log a JIRA we will add a override function to correct it
-
10. Re: Model Date Type for epoch time
See [TEIID-5026] Add function to transform timestamp into Unix time - JBoss Issue Tracker - from_unixtime accepts longs, but it's a value in seconds. The to_millis, and from_millis functions deal with longs as milliseconds.
You should also be able to cast the string value directly to timestamp- cast(FROM_UNIXTIME(...) as timestamp)
-