unix timestamp / epoch conversion in IQ

[Former Member]

Hello,

I searched the community as well as some other historical locations, but couldn't find any discussions on this.

In several of the projects I am working with, there is a source datetime field that always comes in as a unix timestamp (i.e. seconds since the epoch). The question I regularly get is, can we load this into IQ and have it converted during load from the integer into an IQ datetime column.

I have my own suggestions for how to solve this already, but rather than influence the creative (or resolute) mind, I thought I'd put it out to the community to see how you have/would solve this issue?

Thoughts??

Thank you,

-Joe Love

SAP Mobile Services and Consumer Insights

[markmumy]

None that I am aware of during the LOAD TABLE event.  You can always load it and post process the data, but that's gets ugly and requires you to carry an extra column in your table.  My typical approach is to have the data converted before loading into IQ.  If the data is being touched by a streaming engine, ETL tool, or replication, you can do it all inline with those tools and a little coding.  For 3rd party tools, I would have them change their code to generate readable date/time stamps.  That or pre-process the data with something like sed or awk.

Mark

[Former Member]

One option would be to load the data into a staging table and then from there use a view to move the data into the permanent table(s). 

As Mark said, there are a lot of options to do what you want.  The decision comes down to how much data is being loaded, how often and how much effort do you want to put in to find the best method for your environment.

Jason