Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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];
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.
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)