is_snapshot_date

Definition

Setting the property is_snapshot_date on a date attribute will tell Veezoo to interpret dates as points in time, i.e. "2020" will be interpreted as "end of 2020". This is useful in particular for historized data that contains data from the past as well. Veezoo will then answer questions with specific points in time (i.e. on snapshots of the data), by default with the current time.

For example, let's say we have a date attribute "active date" on the class "customers". Normally, the question "Customers active last year" would be answered as "Customers that were active at any point last year". However, this can lead to overcounting customers, as if some customers became inactive during the year while others became active, at no point in time there was that number of customers. Thus, it would be more appropriate to return the "Customers that were active at the end of last year", which is exactly how Veezoo will interpret the question if is_snapshot_date: true is set.

Usage

Configuring a snapshot date is straightforward, just set is_snapshot_date: true on the date attributes that should have this behavior.

kb {
    class Customer {
        /* ... */

        // is_snapshot_date is most often used with date intervals
        date_interval ActiveDate {
            /* ... */
            is_snapshot_date: true
            phrase_name: "active"
        }
    }
}

tip

Set the phrase_name to get a more natural explanation of the answer, for example "Customer active by the end of 2020".

Another typical case where is_snapshot_date would be used is a historized property, for example the status of a customer that can change over time (e.g. "John Doe" has status "New Customer" beginning from January, but status "Lost Customer" beginning from June). Then we would usually also want for a question like "New customers last month" to return only the customers that had status "New Customer" at the end of last month, again to avoid overcounting.

kb {
    class Customer {
        /* ... */
    }

    class CustomerStatusHistory {
        name.en: "Customer Status History"
        // Show only the attributes of CustomerStatusHistory, the class itself has nothing to show
        tag: KB_ShowOnlyRepresenters
        
        from_table: "${CUSTOMER_STATUS_HISTORY}"

        class Status {
            name.en: "Status"
            
            sql: "${CUSTOMER_STATUS_HISTORY}.status"
        }
        relationship of_Customer {
            to: kb.Customer
            
            sql: "${CUSTOMER_STATUS_HISTORY}.customer_id"
        }
        
        date_interval ValidDate {
            name: "Valid Date"
            
            datetime_format: DayFormat
            definition: "interval(this.ValidFrom, this.ValidTo)"
            is_snapshot_date: true
        }
        date ValidFrom {
            name.en: "Valid From"
            hidden: true
            
            sql: "${CUSTOMER_STATUS_HISTORY}.valid_from"
            
            datetime_format: DayFormat
        }
        date ValidTo {
            name.en: "Valid To"
            hidden: true
            
            sql: "${CUSTOMER_STATUS_HISTORY}.valid_to"
            
            datetime_format: DayFormat
        }
    }
}

Behavior

Veezoo will by default try to interpret dates as points in time for snapshot dates.

• Specific dates expressions like "January", "last year" or also "2020 vs 2021" will be interpreted as the end of that date expression
  • In contrast, for the normal non-snapshot dates these would be interpreted as e.g. "at any point in January"
  • Grouping by e.g. month will similarly be resolved to the end of each month
  • "Before" and "after" will be interpreted as "just before" or "just after", e.g. "before 2020" will be interpreted as "at the end of 2019"
• If no date is specified, snapshot dates will be restricted to the current time by default, meaning the current state will be used
• For certain questions where it makes sense, Veezoo will not interpret dates as points in time even for snapshot dates, but interpret them as usually as "at any point in time", e.g. if a user explicitly asks for "at any point in time" or for date ranges