7 Replies Latest reply on Oct 18, 2018 5:58 AM by marc.kusters

Looking to convert timestamp into UNIX epoch value in release 9

avinashch.1611 Jul 4, 2018 8:16 AM

I am looking to convert timestamp to unix epoch value.

I could see that it is implemented as to_millis in release 10 [TEIID-5026] Add function to transform timestamp into Unix time - JBoss Issue Tracker

Is there a way to be able to do conversion prior to this release?

Or any means to find out the timezone of TEIID system from SQL query.

Regards,

Avinash

1. Re: Looking to convert timestamp into UNIX epoch value in release 9

shawkins Jul 4, 2018 3:27 PM (in response to avinashch.1611)

> Is there a way to be able to do conversion prior to this release?

Assuming you want the number of seconds you could use: timestampdiff(SQL_TSI_SECOND, FROM_UNIXTIME(0), timestampValue)

> Or any means to find out the timezone of TEIID system from SQL query.

There's not a great way of doing that unfortunately. The closest would be to enable the ENV function and access the system property for the timezone.
Actions
2. Re: Looking to convert timestamp into UNIX epoch value in release 9

avinashch.1611 Jul 5, 2018 1:31 AM (in response to shawkins)

Thanks a lot Steven for your quick help. It works
Actions
3. Re: Looking to convert timestamp into UNIX epoch value in release 9

avinashch.1611 Oct 10, 2018 6:23 AM (in response to shawkins)

Hi Steven
I am looking to convert currentmilliseconds epoch time into timestamp
I tried using from_unixtime(ts) where ts is the long data-type column storing current epoch time.
It throws error, but i see it supports long data type as parameter.
When i put value = ts/1000, it works fine, but when i use expression as from_unixtime(ts/1000), it throws error.
Any specific reason, it cannot do the division internally?
Actions
4. Re: Looking to convert timestamp into UNIX epoch value in release 9

shawkins Oct 10, 2018 7:55 AM (in response to avinashch.1611)

> Any specific reason, it cannot do the division internally?

Can you provide more details of the error?
Actions
5. Re: Looking to convert timestamp into UNIX epoch value in release 9

avinashch.1611 Oct 10, 2018 8:03 AM (in response to shawkins)

Hi Steven,

Error: TEIID30070 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'FROM_UNIXTIME(public.numerical_metric_data.ts)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.
SQLState: 50000
ErrorCode: 30070
Actions
6. Re: Looking to convert timestamp into UNIX epoch value in release 9

shawkins Oct 10, 2018 8:57 AM (in response to avinashch.1611)

What version are you on, and what is the type of the ts column?

Prior to Teiid 10 an integer was expected as the argument. After that long was accepted.
Actions
7. Re: Looking to convert timestamp into UNIX epoch value in release 9

marc.kusters Oct 18, 2018 5:58 AM (in response to avinashch.1611)
In older versions you can solve this by creating your own procedure. The steps you need to take would be:
Create a new timestamp with date 1970.
Divide milliseconds by 1000 to get to seconds.
Get days by dividing by 86400.
Get remaining seconds with mod(seconds, 86400)
Use timestampadd to combine all those.

This is how we do it currently with RedHat DV. If longs where accepted it would be a lot easier .
Actions

Go to original post