October 29, 2003 at 8:39 am
I see some databases that store date and time fields separately for event or transaction tables, some databases that store the date and time as one field.
What are the pros & cons of using either method?
Thanks in advance...
October 29, 2003 at 10:11 am
Let's say you need to know all the data from a specific date 2003-10-29.
It's easier to 'pull' that from a column that only has a date. Using separate columns, you can do a WHERE mydate = '2003-10-29' and get everything for that date.
But if you need a specific date and time, you need to query two columns:
WHERE mydate = '2003-10-29' AND mytime = '12:00:00'. Then if you need a range of dates and times, the query gets longer:
WHERE (mydate >= '2003-10-29' and mydate < '2003-10-31') AND (mytime > '12:00:00' and mytime < '16:00:00')
Storing Date and time together makes it harder to do the above. But it means that you only have to query one column for the information. For example, to get everything from 10/29/2003, I just use WHERE mydatetime >= '2003-10-29 00:00:00' AND mydatetime < '2003-10-30 00:00:00'.
Finally, if you are only talking about SQL Server, then the option is between using DATETIME datatype (which stores both) or a CHAR or VARCHAR field for date and one for time. CHAR/VARCHAR dates and times cannot be used in Date calculation functions (DATEADD, DATEDIFF, DATEPART, etc)
-SQLBill
October 29, 2003 at 11:14 am
I know I read somewhere that date and time will be separate data types in the next version of SQL Server.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
October 29, 2003 at 11:40 am
Yes, MS intends to separate the date and time into two different data types. A lot of SQL Server users/DBA/programmers, etc. have apparently requested that change. It makes sense to store them separately.
Currently, if I only care about the date, I can not store only the date. DATETIME will add a default time. So, I can't do a search like this:
WHERE mydate = '2003-10-29'.
The database has 2003-10-29 00:00:00 which is not the same as 2003-10-29. I would have to use the search:
WHERE CONVERT(varchar(10), mydate, 121) = '2003-10-29'
Which adds 'overhead' to the query execution. Plus, with both date and time combined I can't ask for everything with the time of 1PM (1300):
WHERE mydatetime = '13:00:00' just won't work. I have to convert the datetime field to varchar and 'chop' off the date for it to work.
Separating the two is the way to go. Hopefully MS does it in a way that will still allow the DATE functions to work.
-SQLBill
October 29, 2003 at 2:27 pm
quote:
Currently, if I only care about the date, I can not store only the date. DATETIME will add a default time. So, I can't do a search like this:WHERE mydate = '2003-10-29'.
Of course you can. If you do not save a time component, SQL server will use zero time, so that predicate works fine.
quote:
The database has 2003-10-29 00:00:00 which is not the same as 2003-10-29.
It is "the same" when used with the above predicate... As I'm sure you know, inserting '2003-10-19' stores that with a zero time component, so using it as a SARG will return what was inserted.
quote:
I would have to use the search:WHERE CONVERT(varchar(10), mydate, 121) = '2003-10-29'
If there exist values with non-zero time components, then instead use something like you had in your first post if there ever may be an index on the column:
WHERE MyDate >= '2003-10-29' AND MyDate < '2003-10-30'
To allow use of an index.
quote:
Which adds 'overhead' to the query execution.
The above has no more overhead than will a predicate on a char column, particularly if you're dealing with ranges.
quote:
Plus, with both date and time combined I can't ask for everything with the time of 1PM (1300):WHERE mydatetime = '13:00:00' just won't work. I have to convert the datetime field to varchar and 'chop' off the date for it to work.
WHERE CONVERT(char(8),MyDate,8) = '13:00:00'
Or, if you're storing just the time component in a temporal data type, your "non-working" predicate works fine:
DECLARE @d datetime
SET @d = '13:00:00'
SELECT CASE @d WHEN '13:00:00' THEN 'True' ELSE 'False' END
quote:
Separating the two is the way to go. Hopefully MS does it in a way that will still allow the DATE functions to work.-SQLBill
If your row has both a date and time, then having both in the same column is the "way to go." It uses less space, keeps the data atomic, and allows use of the temporal functions on the single values. Let's say you want a time stamp (using GETDATE()) on a row in a transaction table; do you really want the date in one column and the time in another? Why? Using the examples in your first post, let's say you want to get all transactions for a single day:
DECLARE @Date datetime
SET @Date = '2003-10-29'
WHERE TranDateTime >= @Date AND TranDateTime < @Date + 1
For a time window with a range of dates:
WHERE TranDateTime >= @StartDate AND TranDateTime < @EndDate + 1
AND CONVERT(char(8),TranDateTime,8) BETWEEN '12:00:00' AND '16:00:00'
The only thing "difficult" about dealing with the current types is staying aware that they contain both date and time and understanding how the zero values display.
--Jonathan
--Jonathan
October 30, 2003 at 8:07 am
Jonathan,
Thanks for the information and 'break-down' of my post and where I went wrong.
-SQLBill
October 30, 2003 at 8:54 am
Thanks to all for your help!
October 30, 2003 at 1:20 pm
Nevertheless, it makes sense that Date and time should be separate, or at least ALLOWED to be separate.
for SQL2k, datetime ranges from January 1, 1753 through December 31, 9999. So, that's:
select (24*60*60*(1000/3.33)) * datediff(dd, '1753-01-01', '9999-12-31')
datetime: 78,153,158,840,765
date only: 3,012,153
Time only: 25,945,945
By separating date and time
1) their functions will be faster, as date and time math can be separated (ie; simplified)
2) ordered lookups will be faster, as there are less possible distinct values.
So, something like:
select customer
where Date = Today
Will run faster than:
select customer
where datetime between '2003-10-30 00:00:00.000' and '2003-10-30 24:59:59.999'
But ranges will (should?) also run faster:
select customer
where Date = Today and Time between CurrentHour - 1 and CurrentHour - 2
And this should be WAY faster:
select customer
order by Date desc
And if you have to order by time as well:
select customer
order by Date, Time
Or ONLY time, which would be useful for traffic analysis:
select customer
order by Time
Makes perfect sense, but I'm sure they'll keep the old datetime around for backward compatibility.
Signature is NULL
October 30, 2003 at 3:25 pm
quote:
Nevertheless, it makes sense that Date and time should be separate, or at least ALLOWED to be separate.
I agree, and I am looking forward to the new types. There are certainly many situations where one needs either dates or times. Please note that this discussion is off-topic unless you are advocating using two columns in the current release.
quote:
for SQL2k, datetime ranges from January 1, 1753 through December 31, 9999. So, that's:select (24*60*60*(1000/3.33)) * datediff(dd, '1753-01-01', '9999-12-31')
datetime: 78,153,158,840,765
date only: 3,012,153
Time only: 25,945,945
(Actually, it's 24*60*60*300. The time component is in ticks.)
I infer that your point is that one can save space if one only needs one or the other. This "argument" works against keeping using two columns in the current release (there I go again trying to stay on topic; sorry). If one needs both in Yukon, I don't think that a date column and a time column will add up to less than the eight bytes of datetime; I doubt that MS will choose to make the range for dates more circumscribed than that of datetime, and I really doubt they'll use three bytes for a type like this...
quote:
By separating date and time1) their functions will be faster, as date and time math can be separated (ie; simplified)
The only performance gain I can foresee is from working with four-byte values rather than eight-byte values. "Date and time math" is just simple arithmetic anyway. Adding a minute to a datetime is simply adding 0x4650 to a four-byte binary value.
quote:
2) ordered lookups will be faster, as there are less possible distinct values.So, something like:
select customer
where Date = Today
Will run faster than:
select customer
where datetime between '2003-10-30 00:00:00.000' and '2003-10-30 24:59:59.999'
No. This is only applicable if the column is indexed, in which case there is no difference.
quote:
But ranges will (should?) also run faster:select customer
where Date = Today and Time between CurrentHour - 1 and CurrentHour - 2
Again, only relevant if the columns are indexed, in which case this particular query would be faster with a datetime.
quote:
And this should be WAY faster:select customer
order by Date desc
Yes, but it is easy to optimize for this if one will be doing a lot of this sort of thing.
quote:
And if you have to order by time as well:select customer
order by Date, Time
Definitely faster with a datetime unless you've got a concatenated index on (Date, Time), where it will be the same as when the datetime is indexed.
quote:
Or ONLY time, which would be useful for traffic analysis:select customer
order by Time
Yes. (But could be optimized for in the current version. Or just use two columns.)
quote:
Makes perfect sense, but I'm sure they'll keep the old datetime around for backward compatibility.
And because it makes more sense than splitting up the two values for many needs. My example of a transaction time is probably representative of the majority of cases where the date and time are both stored, in which case a datetime is indicated. If you have a legitimate need for having both date and time separate, then you can easily do that now by using two datetime columns with zero dates in one and zero times in the other. You can even enforce this with check constraints. They work fine with the temporal functions, and you can just add them together to get the whole again. When would you advocate this and why? That's the OP's question.
--Jonathan
--Jonathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply