anything
AirNav RadarBox
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
 


Author Topic: Yesterday's aircraft count decreasing.  (Read 4675 times)

0 Members and 1 Guest are viewing this topic.

andrewarles

  • Database Updaters
  • Hero Member
  • *****
  • Posts: 2426
Yesterday's aircraft count decreasing.
« on: October 07, 2010, 02:15:07 PM »
Hi,

Could someone explain to me why the aircraft count decreases as you go through the day when you use the Quick Set "Yesterday" in MyLog?

At 12.55 I had 1186 a/c for yesterday
At 13.12 I had 1169 a/c for yesterday
At 16.08 I had 1060 a/c for yesterday

Andrew
16 NM SSW of LFMV (Avignon)
27 NM NW of LFML (Marseille)
Arles, France.

orkney

  • Database Updater Moderator
  • Hero Member
  • *****
  • Posts: 1550
    • orkney radarbox screenshot
Re: Yesterday's aircraft count decreasing.
« Reply #1 on: October 07, 2010, 02:19:36 PM »
Hello

If you receive the same aircraft again today as yesterday the total for yesterday will go down.

Andrew

andrewarles

  • Database Updaters
  • Hero Member
  • *****
  • Posts: 2426
Re: Yesterday's aircraft count decreasing.
« Reply #2 on: October 07, 2010, 02:28:10 PM »
Is it a software bug or is it intentional?
16 NM SSW of LFMV (Avignon)
27 NM NW of LFML (Marseille)
Arles, France.

Chris11

  • Hero Member
  • *****
  • Posts: 1052
Re: Yesterday's aircraft count decreasing.
« Reply #3 on: October 07, 2010, 02:29:03 PM »
I have set up the ANRB so that I get an email of the days aircraft every night after midnight

andrewarles

  • Database Updaters
  • Hero Member
  • *****
  • Posts: 2426
Re: Yesterday's aircraft count decreasing.
« Reply #4 on: October 07, 2010, 02:32:37 PM »
Me too Chris, I just thought it was strange that when you're searching for yesterday's a/c it's not actually all of yesterday's a/c.
16 NM SSW of LFMV (Avignon)
27 NM NW of LFML (Marseille)
Arles, France.

AirNav Support

  • AirNav Systems
  • Hero Member
  • *****
  • Posts: 4124
Re: Yesterday's aircraft count decreasing.
« Reply #5 on: October 07, 2010, 02:32:58 PM »
Its the way the database is recorded.

As the database stores, first and last time recieved. Hence if an aircraft appears today and yesterday the row will store it was seen today and therefore won't appear as yesterday.
Contact Customer/Technical support via:
http://www.airnavsystems.com/contact.html
[email protected]

tarbat

  • ShipTrax Beta Testers
  • Hero Member
  • *
  • Posts: 4219
    • Radarbox at Easter Ross
Re: Yesterday's aircraft count decreasing.
« Reply #6 on: October 07, 2010, 03:12:59 PM »
Its the way the database is recorded.

As the database stores, first and last time recieved. Hence if an aircraft appears today and yesterday the row will store it was seen today and therefore won't appear as yesterday.

Unless you change the way the "Yesterday" query works to look at the first and last times in the Flights table, instead of the Aircraft table.  Simples!!

I've posted the SQL to do this before, but here it is again (the important bit is the use of the Flights table in the WHERE statement):

SELECT DISTINCT
  Aircraft.Registration AS "Reg",
  Flights.Callsign AS "Flight ID",
  Flights.Route AS "Route",
  Aircraft.AircraftTypeSmall AS "ICAO Type",
  Aircraft.Airline AS "Airline",
  Aircraft.AircraftTypeLong AS "Aircraft",
  Flights.StartAltitude AS "Start Altitude",
  substr(Flights.StartTime,1,16) AS "Flight Started",
  Flights.EndAltitude AS "End Altitude",
  substr(Flights.EndTime,1,16) AS "Flight Ended",
  Aircraft.ModeS AS "Mode S"
FROM
 Aircraft
 LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS)
WHERE
  (date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))

ORDER BY
  Aircraft.Registration,
  Flights.EndTime,
  Aircraft.ModeS

ACW367

  • Guest
Re: Yesterday's aircraft count decreasing.
« Reply #7 on: October 07, 2010, 05:05:47 PM »
Rest assured all the aircraft received yesterday will continue to be annotated on the the table generated on the reporter tab, data generated there will not change.