Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Convert unix time stamp to date and time

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Convert unix time stamp to date and time

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

View solution in original post

9 Replies
Highlighted
Specialist
Specialist

Re: Convert unix time stamp to date and time

Try this

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

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

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

Highlighted
Not applicable

Re: Convert unix time stamp to date and time

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

Highlighted
Specialist
Specialist

Re: Convert unix time stamp to date and time

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

Highlighted
Not applicable

Re: Convert unix time stamp to date and time

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

Highlighted
Specialist
Specialist

Re: Convert unix time stamp to date and time

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

Highlighted
Not applicable

Re: Convert unix time stamp to date and time

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

Highlighted
Specialist
Specialist

Re: Convert unix time stamp to date and time

Highlighted
Not applicable

Re: Convert unix time stamp to date and time

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

View solution in original post

Not applicable

Re: Convert unix time stamp to date and time

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