topic Re: Convert unix time stamp to date and time in QlikView

Convert unix time stamp to date and time

Wed, 05 Sep 2012 13:00:11 GMT

Hi ,

I am new to qlik view. The timestamp in table is in unix format.

How do we convert this in to date and time.

Please explain if there is a function and how to implement this function.

Can we use this at the load time by editing the script ?

Regards

Jai

Re: Convert unix time stamp to date and time

rajni_batra — Thu, 06 Sep 2012 07:28:31 GMT

Try this

timestamp(<UnixTimeStamp>/ 86400 + 25569) as TS,

date(floor(<UnixTimeStamp>/ 86400 + 25569)) as Date,

time(frac(<UnixTimeStamp>/ 86400 + 25569)) as Time,

Re: Convert unix time stamp to date and time

Thu, 06 Sep 2012 08:32:40 GMT

Hi Rajni,

Thanks for your reply. But I am not sure where should i add this function. Can you please let us know how to do this.

Many Thanks,

Jai

Re: Convert unix time stamp to date and time

rajni_batra — Thu, 06 Sep 2012 08:34:12 GMT

in the script only where u are loading that data otherwise firstly u can check this in a text box also

Re: Convert unix time stamp to date and time

Thu, 06 Sep 2012 09:29:27 GMT

Hi I am sorry complete novice here, I tried the functions but did not work.

Below is the load code that we are using.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO Qlick;

//-------- Start Multiple Select Statements ------

LOAD `sample_timestamp`;

SQL SELECT `sample_timestamp`

FROM ddsresults.`session_fact_hour`;

//-------- End Multiple Select Statements ------

Here sample_timestamp column has the all the date and time in unix format.

we want to see the data in the normal date and time format.

the example of the unix format is 1341183600000

Regards,

Jai

Re: Convert unix time stamp to date and time

rajni_batra — Thu, 06 Sep 2012 09:42:22 GMT

i have never worked with this but i think

SQL SELECT `sample_timestamp`,

timestamp(`sample_timestamp`/ 86400 + 25569) as TS,

date(floor( `sample_timestamp`/ 86400 + 25569)) as Date,

time(frac( `sample_timestamp` 86400 + 25569)) as Time,

FROM ddsresults.`session_fact_hour`;

should work if this single filed contains date n Time

Re: Convert unix time stamp to date and time

Thu, 06 Sep 2012 10:08:42 GMT

Hi Rajni,

Thanks for your help but when i try i get the following error.

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.1 Driver][mysqld-4.1.22-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `sample_timestamp` 86400 + 25569)) as Time

FROM ddsresults.`session_fact_h' at line 4

SQL SELECT `sample_timestamp`,

timestamp(`sample_timestamp`/ 86400 + 25569) as TS,

date(floor( `sample_timestamp`/ 86400 + 25569)) as Date,

time(frac( `sample_timestamp` 86400 + 25569)) as Time

FROM ddsresults.`session_fact_hour`

I tried removing the "time(frac( `sample_timestamp` 86400 + 25569)) as Time" and the code runs. I assumed that this particular line is missing "/" in the function and there is an extra , at the end.

Even with these changes the code gives me an error as above and when i remove this line and run. the code runs but the results in the sample timestamp are still in unix time stamp format.

Please help.

Regards,

Jai

Re: Convert unix time stamp to date and time

rajni_batra — Thu, 06 Sep 2012 10:18:34 GMT

http://community.qlik.com/search.jspa?peopleEnabled=true&userID=&containerType=&container=&spotlight=true&q=unix+format

have a llok on this thread may be u get answer of ur question

Re: Convert unix time stamp to date and time

Wed, 12 Sep 2012 14:10:04 GMT

Hi ,

The above suggestions do not work. I did manage to get this working as below:

ConvertToLocalTime(timestamp((25569+(sample_timestamp+3600000)/(1000*24*3600))), 'GMT-01:00').

Hope this helps others.

Regards,

Jai

Re: Convert unix time stamp to date and time

Wed, 12 Sep 2012 14:16:48 GMT

Now that I have the date and time in the correct order.I want the data to be aggregated based on the timestamp.

For example:

During

12/08/2012 08:00:00 -----------Product1------------12 sales occured.

12/08/2012 08:00:00 -----------Product2------------3sales occured.

12/08/2012 09:00:00------------Product1------------2sales occured.

12/08/2012 09:00:00------------Product2------------3sales occured.

This means between 7 and 8 12 sales occured and between 8 and 9 5 sales occured.

But when I create a straight table, I get the data this way.

12/08/2012 08:00:00 ---------Product1--------------14 sales occured.

12/08/2012 08:00:00 -----------Product2------------6 sales occured.

12/08/2012 09:00:00-----------Product1----------------14 sales occured.

12/08/2012 09:00:00-----------Product2----------------6 sales occured.

The data is getting aggregating based on products . Can any one help me with this please?

Regards,

Jai