4 Replies Latest reply: Mar 8, 2015 11:30 PM by Hannah Chan RSS

    Unix time stamp in where clause

    Hannah Chan

      Hi there,

       

      I am loading a sql table with unix_time stamp in it.

      I have converted the unix_time by the script below

      ConvertToLocalTime(timestamp((25569+(created+3600000)/(1000000000*24*3600))), 'GMT+08:00') as created

       

      Now I want to load data which only falls between certain time stamp by defining the time frame in where clause.

      But I don't know how to do it.

      I wrote a script like below, but it doesn't work.

      How should I define the right time in the where clause for fields which are converted unixtime?

       

      Load <field A>,

               <field B>,

       

                  ConvertToLocalTime(timestamp((25569+(<myunix_time field>+3600000)/(1000000000*24*3600))), 'GMT+08:00') as created;

      SQL     Select *

      From <mysqltable>

      Where created >'4/03/2015 21:00:00';

       

       

       

       

       

       

       

       

       

       

       

        • Re: Unix time stamp in where clause
          Gysbert Wassenaar

          If your sql source contains a 'created' field then you can use a where clause with the created field. If your sql source table does not contain a 'created' field then you can't do that, but you will need to use the original unix timestamps from the 'myunix_time' field. What kind of function you need to convert the unix timestamp to a date depends on what database system you're using. SQL Server, Oracle, MySql etc all have different functions for dealing with unix timestamps.