Why not store date/time seprately (1 Viewer)

[speakers_86]

I have read that it should be stored in one field as a date/time, but in the front end, I have a date picker for the date, and a separate time field. Why would I not store it separately?

 

[pbaldy]

What kind of queries will you run against the data? Things like calculations across midnight, records coming up in x minutes/hours/whatever are easier with a single field. It could be argued that you're taking a single piece of data, a point in time, and breaking it into 2 pieces. I don't have a strong feeling on the subject, but I've done it both ways and I use a single field now.

 

[Alc]

If you're going to refer to the same entry for both the date and time value (e.g. the timestamp for when a record was updated on a system) then it makes sense to store it as one value, since both the data and the time can be derived from the single entry.

However, if it were necessary to store it separately (e.g. for an application booking appointments at a doctor's office, where you might want the ability to select 60 minute blocks of time), it might make sense to store them separately.

In either case, you don't HAVE to store them together, it just makes sense a lot of the time.

 

[speakers_86]

These are just notes that have date and time logged. I have a dynamic search form that allows users to search for entries between given dates and times.

 

[gemma-the-husky]

I think it makes a lot of sense to store the details separately.

we often talk about not have structured id's and keeping constituent parts separate. In truth in many systems a date and a time are there for different reasons. The time need not even be a time datatype, as a fixed length string would sort correctly.

 

[speakers_86]

It's funny you say that, because that is exactly how I'm doing it now.

 

[The_Doc_Man]

The ONLY reason you would store date and time together is that you wanted to compute an interval in seconds (or minutes or hours) between two arbitrary timestamps, which you can do with a pair of date/time variables.

I find, however, that since queries are equally good (if not better) for providing recordsets for my forms, I can just use an INT() function on a date/time variable to get the date portion by itself. (That is NOT a CInt function...)

The thing is, it all depends on that to which you are accustomed. If you are happy with date/time manipulation with a single variable, you'll keep it all in one variable. If not, you won't.

 

[Galaxiom]

Efficient searching by date range requires an index on the field. Indexing a DateTime field will result in a vast number of index values, one for every DateTime in the indexed field, potentially as many as 86,400 per day. This is a big overhead compared to indexing the Date alone.

 

[gemma-the-husky]

surely in practice you will only get a handful of times in a given day, depending on the size of a system. you can select by date, and then sort the selected data set. It's probably quite efficient. It can't be much less efficient than having to subject every date/time to a function such as int() to extract just the date.

the trouble with date times, is that the date part is discrete, but the time part isn't.

 

[Galaxiom]

surely in practice you will only get a handful of times in a given day, depending on the size of a system.

Definitely depends on the system. Where I work the main table inserts 15,000 records with over 500 unique times per day and we only have a one minute time resolution. Imagine how that number, and hence the index values, could grow over a few years of data if it were one second resolution.

you can select by date, and then sort the selected data set. It's probably quite efficient.

Sure an index could work efficiently for the select but it would be a big overhead maintaining it for DateTime.

It can't be much less efficient than having to subject every date/time to a function such as int() to extract just the date.

I think we are arguing from the same side.