| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-04-07 : 03:58:49
|
| I want to write a timeline application that you can use to log events against times in the future or past.It needs to deal with dates (preferable to the second) of up to 1500 into the future from 2000 CE to 7000 years back (5000 BCE).Any brains out there that can maybe guide me? Can you create a custom datetype that I can set a column to?I could just use a Long to represent every second in that time frame (60 seconds X 60 minutes X 24 hours X 365 days X 8500 years = 268 056 000 000) but is there a more elegant way?Then I also have to deal with leap years and the fact that there is no year zero, etc.And then later I may look at being able to show the same date in other types of Calendars (eg Jewish, Chinese...) |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-07 : 05:47:52
|
in times like the old ages, when there were dragons upon the land an heros were sparse the mighty datetime has began it's evolution. it was threefold at the begining. then it said: i'd be better if i was in seven parts. so he became 7 integers.the most precise was the power of millisecond, then ms's lower version second, minute and hour.then came the old spells that held great power: day, month and year. for centuries they ruled the earth until the modern ages.you can use doomsday algorithm for date calculation in different calendars.serach it on google.Go with the flow & have fun! Else fight the flow  |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-04-07 : 06:38:59
|
| I am curious as to why it is needed to have second level precision in dates that go back thousands of years?-------Moo. :) |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-07 : 06:52:42
|
| Ok, take seconds out. But I'd need the rest for current/recent dates. So I assume you cant have that level of precision for some dates and not others... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-04-07 : 06:55:49
|
| That is a good point.-------Moo. :) |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-05-22 : 11:52:24
|
| Looks like I could just use Oracle. They go back as far as I need:http://database.ittoolbox.com/pub/TP012003.HTML |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-22 : 11:55:52
|
still having a problem with this?  or wait for katmai datetime2 datatype_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-05-22 : 13:27:59
|
| If you really need to have dates as far back as 5000 BCE, it looks like neither Oracale (min 4713 BCE) nor SQL 2008 (min 0001-01-01) will do what you want.CODO ERGO SUM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-22 : 13:53:12
|
| That was great Mladen!I find it curious that this (datetime) topic has a three year gap but continued on as if no time had passed. Coolerbob, are you a time traveler?>>but is there a more elegant way?It may not be more elegant but have you considered encoding a binary value with a representation of this BIGDATETIME?You could support it with your own bigdatetime functions (bigdateadd, bigdatediff, bigdatename, bigdatepart, etc..) ie: maybe different byte positions (converted to ints) representing different offsets from...something.I'm just wondering if it's even possible for someone from 7000 years ago to "have a case of the Mondays".EDIT:of course you'd need a CONVERT for formatting output unless you handled that elsewhere.Be One with the OptimizerTG |
|
|
|
|
BIG Datetime