5 Replies Latest reply: Dec 28, 2015 4:44 PM by Marco Wedel RSS

    convert timestamp to date

      I'm trying to convert Epoch time to both a timestamp. Here is what my script looks like:

       

      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';
      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
      SET FirstWeekDay=6;
      SET BrokenWeeks=1;
      SET ReferenceDay=0;
      SET FirstMonthOfYear=1;
      SET CollationLocale='en-US';
      
      [Table1]:
      LOAD
          *,
          Date(Floor([Timestamp])) as [Date],
          Second([Timestamp]) as [Second],
          Minute([Timestamp]) as [Minute],
          Hour([Timestamp]) as [Hour],
          Day([Timestamp]) as [DayOfMonth],
          Month([Timestamp]) as [Month],
          Year([Timestamp]) as [Year];
      
      FOR EACH vFile IN FILELIST('*.csv')
          CONCATENATE
          LOAD
              Timestamp(Timestamp('01/01/1970 00:00:00.000') + @1/60/60/24) AS [Timestamp]
          FROM [$(vFile)]
          (txt, codepage is 1252, no labels, delimiter is ',', msq);
      NEXT vFile;
      

       

       

      The timestamps look like

       

      1450123265.22
      1450124764.55
      1450125364.76
      1450570264.74
      

       

      The issue is that only a few of the timestamps are getting converted to dates in the [Date] field. For example of the 4 timestamps above the first 3 result in [Date] fields of NULL. The last one parses to "12/20/2015". I'm not sure what's causing the inconsistencies in the parsing.

       

      Any help is appreciated.