Re: Storing dates (was: Re: Ticket Ordering Question)

This WebDNA talk-list message is from

2003

It keeps the original formatting. numero = 54991
interpreted = N
texte = > >...Also, it hasn't been brought up lately on the list, so I'll >mention that most experienced DNA'ers store their dates and times as >numerical values (e.g. store the [math]{[date]}[/math] value instead >of the [date] value) in their databases whenever they can. This >makes searches, sorts and comparisons much easier and faster, and >you can format the value for display in any way you wish using the >[format days_to_date ...] context.>>Uhhh... Yeah I bought into that conspiracy a couple of years ago >too. "store dates as integer" Today, I say baloney. Once you >understand how WebDNA handles dates, searching for a date or doing >date math is just as easy using mm/dd/yyy notation. I suspect the >whole idea of "store dates as integers" came about because WebDNA's >date math was not understood. There may have even been a bug in >WebDNA that forced people to use integers instead of dates. I'm sure >that's no longer the case.Ease of programming and software performance are sometimes very opposite things.The reason this technique was suggested for a very long time (several years ago) is because webdna must convert every formatted date value in the db field to a number *before* it can be compared in a search. In older versions of the software this took MINUTES (not seconds) when there were tens or hundreds of thousands of records in the db.Webdna still has to do the same thing today, the only difference between today and years ago is that the software speed has been improved when converting formatted date values to numbers.Let's say you want to do a search on the "dbDate" field where your field values are formatted like MM/DD/YYYY, and your search parameter looks like this:&ledbDatedatarq=10/21/2003&dbDatetype=dateHere's what webdna has to do in this situation:1- Convert your formatted date parameter to a number.2- Retrieve the formatted date values from the dbDate field of *every* record in the db.3- Dynamically convert *every* [dbDate] value to a number, then compare that number to the numeric value of the date in your search parameter.4- Identify and retrieve every record with a numeric date value that is "less than or equal to" the numeric date value of your search parameter, then display the results in the founditems context.Obviously this is a lot more work for webdna than if the dates were stored as numbers in the first place. Therefore webdna will always perform the search FASTER if the dates are stored as numbers. But you will probably never notice the speed difference when using relatively small db files.-- Kenneth Grome-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Associated Messages, from the most recent to the oldest:

> >...Also, it hasn't been brought up lately on the list, so I'll >mention that most experienced DNA'ers store their dates and times as >numerical values (e.g. store the [math]{[date]}[/math] value instead >of the [date] value) in their databases whenever they can. This >makes searches, sorts and comparisons much easier and faster, and >you can format the value for display in any way you wish using the >[format days_to_date ...] context.>>Uhhh... Yeah I bought into that conspiracy a couple of years ago >too. "store dates as integer" Today, I say baloney. Once you >understand how WebDNA handles dates, searching for a date or doing >date math is just as easy using mm/dd/yyy notation. I suspect the >whole idea of "store dates as integers" came about because WebDNA's >date math was not understood. There may have even been a bug in >WebDNA that forced people to use integers instead of dates. I'm sure >that's no longer the case.Ease of programming and software performance are sometimes very opposite things.The reason this technique was suggested for a very long time (several years ago) is because webdna must convert every formatted date value in the db field to a number *before* it can be compared in a search. In older versions of the software this took MINUTES (not seconds) when there were tens or hundreds of thousands of records in the db.Webdna still has to do the same thing today, the only difference between today and years ago is that the software speed has been improved when converting formatted date values to numbers.Let's say you want to do a search on the "dbDate" field where your field values are formatted like MM/DD/YYYY, and your search parameter looks like this:&ledbDatedatarq=10/21/2003&dbDatetype=dateHere's what webdna has to do in this situation:1- Convert your formatted date parameter to a number.2- Retrieve the formatted date values from the dbDate field of *every* record in the db.3- Dynamically convert *every* [dbDate] value to a number, then compare that number to the numeric value of the date in your search parameter.4- Identify and retrieve every record with a numeric date value that is "less than or equal to" the numeric date value of your search parameter, then display the results in the founditems context.Obviously this is a lot more work for webdna than if the dates were stored as numbers in the first place. Therefore webdna will always perform the search FASTER if the dates are stored as numbers. But you will probably never notice the speed difference when using relatively small db files.-- Kenneth Grome-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Kenneth Grome

DOWNLOAD WEBDNA NOW!

Re: Storing dates (was: Re: Ticket Ordering Question)

2003

Top Articles:

Related Readings: