Cannot extract only some parts of a timestamp

[#1]

In table Stamps, I store all the hours (as a timestamp i.e. date with time) in 2014. So, I have 365 * 24 = 8760 rows in the table. I want to extract only one of the parts of a timestamp, at a time -

(1) Date only

(2) Time only

(3) Hours only

In tried to do (1) first. I was hoping it would give me 365 rows, but it gives me 8760 instead. How do I correct this and how do I do (2) and (3) ?

Code for (1) -

[Tmp_Dates_2014]:

LOAD Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

Thanks.

[swuehl · Accepted solution]

Are you looking for a DISTINCT load maybe?

[Tmp_Dates_2014]:

LOAD DISTINCT Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

LOAD DISTINCT Time(Frac(TStamp)) as [TimeOnly]

Resident [Web Traffic] Order By [TStamp];

LOAD DISTINCT Hour(TStamp) as [HourOnly]

Resident [Web Traffic] Order By [TStamp];

[#3]

Thanks ! That was fast   Makes sense - when you strike off the time part from a date, only the date part remains and the date is repeated 24 times. What was I thinking.

[maxgro]

1) add a distinct

[Tmp_Dates_2014]:

LOAD distinct

Date(Floor(TStamp)) as [DateOnly]

Resident [Web Traffic] Order By [TStamp];

2) distinct time#(time(TStamp, 'hh:mm:ss'))

3) distinct hour(TStamp)