6 Replies Latest reply: Sep 25, 2012 2:55 PM by Johan Adolfsson RSS

    Problem formatting time...i think

      Hi

      I am looking for help with the code below. I don't understand where I am going wrong with this.

      My guess is it is a formatting problem with time ie. my_time, something I am doing wrong.

      What I want to achieve is to be able to display if a specific time is in the morning, day, evening or night.

      I am reading many timestamps from an excel spredsheet. See attached qvw...

       

      I added the excel file, maybe that will help.

       

      Thankful for any help.

       

       

      TEMP_TEST_TIMESTAMP:

      LOAD

                @1 as my_Timestamp_original,

                Timestamp(@1, 'YYYY-MM-DD hh:mm:ss') as my_Timestamp,

        Date(Daystart(timestamp(@1)), 'YYYY-MM-DD') as my_date,

        Time(Timestamp(@1, 'YYYY-MM-DD hh:mm:ss'), 'hh:mm:ss') as my_time,

                len(@1) as my_Timestamp_original_length

      FROM

      LB179_20120924_194125.xls(biff, no labels, table is Sheet1$);

       

      TEST_TIMESTAMP:

      load *, len(my_Timestamp) as my_Timestamp_length Resident TEMP_TEST_TIMESTAMP;

       

      drop table TEMP_TEST_TIMESTAMP;

       

      TIMEPERIOD:

      LOAD * INLINE [

      TimeFrom, TimeTo, TimePeriod

      00:00:00, 06:00:00, Night

      06:00:01, 10:00:00, Morning

      10:00:01, 18:00:00, Day

      18:00:01, 23:00:00, Evening

      23:00:01, 23:59:59, Night

      ];

       

      Left join (TEST_TIMESTAMP)

      IntervalMatch(my_time)

      LOAD time(TimeFrom, 'hh:mm:ss') as TimeFrom, time(TimeTo, 'hh:mm:ss') as TimeTo RESIDENT  TIMEPERIOD;

       

      Left Join (TEST_TIMESTAMP)

      LOAD * RESIDENT TIMEPERIOD;

       

      drop table TIMEPERIOD;