3 Replies Latest reply: Feb 1, 2017 2:41 AM by Aishwarya Shetty RSS

    Timestamp

    Aishwarya Shetty

      Hello All,

       

      I have a Timestamp field in my data and I would want to create a new field say X that should have values Morning, Afternoon, Evening. How can I segregate my timestamp into these values?

       

      ex :

      TimestampX
      31-01-2017 12:13:14 PMAfternoon
      31-01-2017 6:05:12 AMMorning
      31-01-2017 7:06:12 PMEvening

       

       

      Thanks

        • Re: Timestamp
          Kaushik Solanki

          Hi,

           

          You need to use If statement to create the New Field. Like below.

           

          Load Timestamp1,if(TimeInNumber <= num(MakeTime(11,59)),'Morning',if(TimeInNumber > num(MakeTime(11,59)) and TimeInNumber <= num(MakeTime(15,59)),'Afternoon','Evening')) as NewField;

          Load timestamp(Timestamp#(Timestamp1,'DD-MM-YYYY hh:mm:ss TT')) - Floor(timestamp(Timestamp#(Timestamp1,'DD-MM-YYYY hh:mm:ss TT'))) as TimeInNumber,* inline [

          Timestamp1

          31-01-2017 12:13:14 PM

          31-01-2017 6:05:12 AM

          31-01-2017 7:06:12 PM

          ];

           

          Regards,

          Kaushik Solanki

          • Re: Timestamp
            Manish Kachhia

            You can use something like below..

            Change StartTime and EndTime according to your need for Morning, Afternoon, Evening and Night flags.

             

             

            Flag:

            Load

              Time(Time#(StartTime,'hh:mm:ss')) as StartTime,

              Time(Time#(EndTime,'hh:mm:ss')) as EndTime,

              Flag

            Inline

            [

              StartTime, EndTime, Flag

              00:00:00, 11:59:59, Morning

              12:00:00, 16:59:59, Afternoon

              17:00:00, 19:59:59, Evening

              20:00:00, 23:59:59, Night

            ];

             

             

            Time:

            Load

              *,

              Time(Frac(TimeStampField)) as TimeStampFieldTime

            ;

            Load

              TimeStamp(TimeStamp#(TimeStampField,'DD-MM-YYYY h:mm:ss TT')) as TimeStampField

            Inline

            [

              TimeStampField

              31-01-2017 12:13:14 PM

              06-05-2017 6:05:12 AM

              31-01-2017 7:06:12 PM

            ];

             

             

            Inner Join

            IntervalMatch(TimeStampFieldTime)

            Load StartTime, EndTime Resident Flag;

             

             

            Left Join (Time)

            Load * Resident Flag;

             

             

            Drop Table Flag;

            Drop Fields StartTime, EndTime;