Dates are funny things. First you think they are something to do with days of the year then you turn around and they are numbers. What do numbers and dates have in common?
Internally all dates, including the time portion of a day, are stored as a certain number of days since a base date. We are not really concerned with that we are more interested in the fact that 0.5 of a day is midday and 0.0 means midnight. All the other hours can be worked out once we realise that 1/24 is the same as saying 1 am.
This concept allows the accurate, to seconds - not tiny fractions of seconds, caculations on dates to be performed. We can state reliably that one date less another will give a number. We can add, or subtract, a number from a date. We cannot however add two dates together as this is quite meaningless, both to us as observers and internally to the database.
A date plus or minus a number gives a date, whereas a date less a date gives a number. The number result can be more meaningfully expressed as an interval. Luckily for us there are several built in functions to display this more meaningfully.
As an aside, I did once spend a few days writing routines to extract the difference between two dates and give a meaningful result back days:hours:min:sec. Then someone more junior pointed out the interval functions to me. My embarasment soon passed, when I realised that I now had interval routines that worked, and that maybe relying on past knowledge meant I was missing something that could make my life easier.
No comments:
Post a Comment