Results 1 to 5 of 5
  1. #1
    Berlinomatt is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    1

    Finding gaps in a timeseries

    I'm a little bit new to access so forgive me if I ask stupid questions. I'm currently compiling weather station data and have a database with approx. 1.5 million entries. The data is recorded every five minutes however there are gaps in the timeseries and I'm trying to find a way to identify where they are based on the timestamp. Is there a simple method for this? Here is an example of the dataClick image for larger version. 

Name:	data.JPG 
Views:	16 
Size:	155.3 KB 
ID:	42613


    Many thanks,

    Matt

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,690
    What do you want to do once you know which slots are missing
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,027
    A couple of general approach thoughts for consideration that would highlight issues
    Using the minute value of the records
    'determine if the minute value is divisible by 5 for general cleanliness
    ' check if the minute value is 0 (on the hour) then previous value should be 55
    'check for 5 minutes between values rec(i) -rec(i-1)

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,575
    you can do this with a query to identify records which do not have one that follows in 5 minutes by joining the table to itself on location.

    However as with this post with much the same question

    https://www.accessforums.net/showthr...acr%2C+missing

    you need to be clear about the data you have. Your tablename would appear to imply a single location, but that infringes normalisation best practice. but based on what you have provided - i.e. there is only one location/measuring point in the table the solution might be as below. Also be aware the code provided below only looks for a single instances of a missing time - if there are two or more missing times next to each other, only the first one is reported - and of course the very last record will be reported since there is no later record. So as Bob says - you need to be clear about what it is you want reporting.

    Code:
    SELECT dateadd("n",5,S.measurementtime) AS Missing
    FROM Weatherstation AS S LEFT JOIN Weatherstation AS E ON dateadd("n",5,S.measurementtime)= E.measurementtime
    WHERE E.measurementtime is null
    Note this uses a non standard join so cannot be created in the query builder, you need to use sql. But there is a small cheat. In the query builder, just join on the measuremetime field and then go to sql builder and replace the join.

    You say you have 1.5m records - so make sure your measurementtime field is indexed otherwise it will be painfully slow

    Time can also be quite difficult to join on because it is stored as a decimal number - the number of seconds divided by 86400, the number of seconds in a day - so 5 mins=300 seconds/86400 is a pretty small number and you might just get some rounding differences - you'll need to try it and see.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  2. Find gaps in Dates
    By soldat452002 in forum Queries
    Replies: 5
    Last Post: 07-31-2016, 03:57 PM
  3. Chart gaps in Access
    By clancy76 in forum Access
    Replies: 2
    Last Post: 12-31-2014, 10:48 AM
  4. Closing Up Gaps in a Chart
    By Paul H in forum Programming
    Replies: 1
    Last Post: 08-03-2012, 02:10 PM
  5. Removing Gaps from Report
    By caseym in forum Reports
    Replies: 2
    Last Post: 03-25-2011, 04:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums