shift differential calculation

[#1]

Hello,

I have big troubles with calculation some values, because our company wants that i calculate data by date, BUT where the day begins with 6am and ends with 6am on the next morning.

How can i calculate this differential when i have seperate data for year, month, day, hour, minute?

Thanks, Armin

[MarcoWedel]

subtract 6 hours from your timestamps and load the result as a new field.

[MarcoWedel]

something like:

LOAD *,

          Week(Date) as Week,

          Month(Date) as Month,

          Year(Date) as Year;

LOAD timestampfield,

          DayName(timestampfield-'06:00:00')) as Date,

          someotherfields

FROM yoursource;

[MarcoWedel · Accepted solution]

or as you already have seperate fields for year, month, day, hour, minute:

LOAD *,
          Week(Date) as Week,
          Month(Date) as Month,
          Year(Date) as Year;
LOAD *,
          DayName(timestampfield-'06:00:00')) as Date;
LOAD Timestamp(Makedate(year, month, day)+Maketime(hour, minute)) as timestampfield,
        someotherfields
FROM yoursource;

hope this helps

regards

Marco