9 Replies Latest reply: Sep 12, 2012 10:16 AM by jayanth2 RSS

    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

        • Re: Convert unix time stamp to date and time
          Rajni Batra

          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

              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

                  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

                      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

                        • Re: Convert unix time stamp to date and time
                          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

                            • 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

                    • 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

                      • 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