Subject Re: complex indexes vs index key fields
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 31 Jul 2020 10:44:49 +0200
Newsgroups dbase.getting-started
Attachment(s) create_all_timestamps.prg

On 2020-07-30 08:09, Gaetano De Luisi wrote:
>
> Hi Mervyn,
>
> Brilliant, it took only 10.62 seconds to load the 25k records!
>
> Thanks for that sample code!

You will also need a table of all the possible timestamps to check for
missing timestamps in your data.

The attached program should take longer than that (but probably under a
minute) to create a year's worth of entries as it has to do the INSERT
106 times.  On the other hand it only needs to be run once a year so
this shouldn't be a problem.

RIGHT JOINing the table to the energydata table for a given time span
will tell you if there are any missing timestamps.  It will, however,
not tell which siteID the missing timestamp belongs to.  If there are
any missing timestamps you will need to select the records for each
siteID in turn and RIGHT JOIN the table.  This will identify any missing
records for that specific site.  SOD's Law decrees that the missing
records will always be in the last site(s) tested. :-)

Mervyn.



clear
s = seconds()
d = new adodatabase()
d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata"
d.active = true
try
  cStr = "CREATE TABLE all_timestamps ( "
  cStr += "  eTimestamp TIMESTAMP(6) NOT NULL,"
  cStr += "  PRIMARY KEY USING BTREE (eTimestamp))"
  d.ExecuteSQL(cStr)
catch(exception e)
  ?e.message, 'Table exists'
endtry
nYear = 2020   //edit for following years
nMonth = 0
nDay = 1
nHour = 0
nMin = 0
dDate = new date(nYear,nMonth,nDay,nHour,nMin,0)
dEndDate = new date(nYear+1,nMonth,nDay,nHour,nMin,0)
_app.allowYeildOnMsg = true
n = 0
nInsert = 1
do while dttoc(dDate) < dttoc(dEndDate)
   n1 = 1
   cStr = "insert into all_timestamps (eTimestamp) values "
   do while n1 < 1000 and dttoc(dDate) < dttoc(dEndDate)
      cStr += "('"+dDate.getYear()+"-"+str((dDate.getMonth()+1),2,0,'0')+"-"
      cStr += str(dDate.getDate(),2,0,'0')+" "
      cStr += str(dDate.getHours(),2,0,'0')+":"+str(dDate.getMinutes(),2,0,'0')+":00'),"
      nMins = dDate.getminutes()
      if nMin < 55
         nMin += 5
      else
         nMin = 0
         if nHour < 23          
            nHour ++
         else
            nHour = 0
            nDay ++
         endif  
      endif
      dDate.setYear(nYear)
      dDate.setMonth(nMonth)
      dDate.setDate(nDay)
      dDate.setHours(nHour)        
      dDate.setminutes(nMin)
     _app.executeMessages()
     n++
     n1++    
   enddo
   cStr = substr(cStr,1,len(cStr)-1) //get rid of last comma
   ?'insert',nInsert+' of 106'
   nInsert ++
   d.ExecuteSQL(cStr)
enddo
d.ExecuteSQL('commit')
d.active = false
? n,' Records added '
?'seconds',(seconds()-s)