Convert unix time stamp to date and time

[#1]

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

[rajni_batra]

Try this

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

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

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

[#3]

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

[rajni_batra]

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

[#5]

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;

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

[rajni_batra]

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

[#7]

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

[rajni_batra]

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

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

[#9 · Accepted solution]

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

[#10]

Hi

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