Looking to convert timestamp into UNIX epoch value in release 9

[avinashch.1611]

Hi

  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

[shawkins]

> 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.

[avinashch.1611]

Thanks a lot Steven for your quick help. It works

[avinashch.1611]

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?

[shawkins]

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

Can you provide more details of the error?

[avinashch.1611]

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

[shawkins]

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.

[marc.kusters]

In older versions you can solve this by creating your own procedure. The steps you need to take would be:

1. Create a new timestamp with date 1970.
2. Divide milliseconds by 1000 to get to seconds.
3. Get days by dividing by 86400.
4. Get remaining seconds with mod(seconds, 86400)
5. 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 .