Timestamp Range Lookup -- Help Please

[#1]

Hi Folks, I am trying to add the SHIFT_NAME based on the Timestamp. Below is the time Ranges.

6AM - 6PM --> SHIFT A

6PM - 6AM --> SHIFT B

Please find the below some Sample data:

ID  TIMESTAMP                 SHIFT_NAME

1   5/30/2013 07:10:10 AM   5/30/2013-A

2   5/30/2013 05:59:59 AM   5/29/2013-B

3   5/30/2013 02:19:68 AM   5/29/2013-B

4   5/29/2013 05:10:10 PM   5/29/2013-A

5   5/29/2013 12:10:34 PM   5/29/2013-A

6   5/29/2013 09:10:10 AM   5/29/2013-A

The Bold values are desired out put.

I have very large data set around 4M of data. So please provide best optimal way to achieve this.

[vivientexier]

LOAD

     Date(TIMESTAMP, 'D/MM/YYYY') & If(Hour(TIMESTAMP)>=6 and Hour(TIMESTAMP)<18, '-A', '-B') as SHIFT_NAME

From ...

[#3 · Accepted solution]

Thanks Vivien.

LOAD IF(HOUR(TIMESTAMP) < 6,DATE(TIMESTAMP-1)&'-B',IF(HOUR(TIMESTAMP)>=6 AND HOUR(TIMESTAMP)<=18,DATE(TIMESTAMP)&'-A',DATE(TIMESTAMP)&'-B')) AS SHIFT;

[vivientexier]

auto-congratulation ?! interesting.