| Author |
Topic |
|
joejoe
Starting Member
2 Posts |
 Posted - 2002-01-10 : 04:55:14
|
| This has to be a common problem. We have a Java system storing some events in a log table using the timestamp standard (#milliseconds since 1 Jan, 1970).The log table is the fact table of my cube. As one dimension I want time in 1,2,3,4,5,12 hours, days and weeks. How can this be done?Through the "select dateadd" query I can transform a timestamp to a date, and probably also to a selection with year, month, day, hour, minute from a timestamp. But how can I use this for making the dimension as described? And is there another way commonly used?Would appreciate any advice!/Joey |
|
|
joejoe
Starting Member
2 Posts |
Posted - 2002-01-10 : 10:28:06
|
| Well, now I've come so far that I've made a solution that might not be that brilliant, but it works for now. By adding a field for each date part I need to the log table, and setting the forumulas below for each field, the table is filled with calculated date parts.Any input on how this affects the SQL database, does it cause more work or are the formula answers generated at query time?totalDate = dateadd(minute,actualTime/60000,'1970-01-01'),theYear = year(dateadd(minute,actualTime/60000,'1970-01-01')),theMonth = month(dateadd(minute,actualTime/60000,'1970-01-01')),theDay = day(dateadd(minute,actualTime/60000,'1970-01-01')),theHour = DATEPART(hour,dateadd(minute,actualTime/60000,'1970-01-01')),theMinute = DATEPART(minute,dateadd(minute,actualTime/60000,'1970-01-01')),theSecond = DATEPART(second,dateadd(minute,actualTime/60000,'1970-01-01'))from eventLogTable |
 |
|
|
|
|
|
Drilling with Java timestamp!