I was amazed the influence on how you compare dates would be a factor 1000 change in fetch times even though I wrote about date, time and timestamp conversions before in Source: Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes.
All queries were ran at 20170410.
Slow
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- slow: prepared 0.066 sec; fetched 10.549 sec 250 rows
and date '1899-12-30' + data.datadate >= '2017-04-08' -- last 2 days
and date '1899-12-30' + data.datadate <= '2017-04-10' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.063 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date 'Now' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date 'Now' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.070 sec; fetched 0.011 sec 250 rows
and data.DATADATE >= date '2017-04-10' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date '2017-04-10' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.073 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date '2016-04-08' - date '1899-12-30' -- specific date range start
and data.DATADATE <= date '2016-04-10' - date '1899-12-30' -- specific date range end
The table looks like this:
CREATE TABLE "DATA" ( ID INTEGER NOT NULL, DATADATE DOUBLE PRECISION, DATATIME DOUBLE PRECISION ); CREATE ASC INDEX DATA_DATADATE ON "DATA" (DATADATE);
–jeroen
Need to check out which weather widgets I can get working for a really long period of time with a refresh every minute. [
Good job PlasticSCM! Imposing limits on what you can write. Not!
Finally I need to find out the cause of the below message when I click on “Checkin” (i.e. not Checkout):
The Wiert Corner - irregular stream of stuff 