date

Definition

Date is used whenever you have a column or SQL expression that returns a partial (e.g. Year) or fully qualfied date (e.g. 2020-01-06) or datetime.

Usage

For date, Veezoo assumes that the sql is a SQL date/datetime expression. This means that in case it is not, you should change sql to be a casting operation in your SQL dialect, e.g. sql: "TO_DATE(${ORDERS.date_str}, 'YYYYMMDD')" or directly in your database.

It is also required to specify the granularity by setting datetime_format. This is usually inferred by Veezoo when importing your schema. There exist the following options:

• YearFormat (e.g. 2020)
• DayFormat (e.g. 2020-10-01)
• TimeFormat (e.g. 2020-10-01 13:00:00)
• DayOfWeekFormat (e.g. Friday)
• MonthDayFormat (e.g. 10-24, calendar day with unspecified year, such as for a birthday)
• MonthFormat (e.g. 1969-08)
• YearQuarterFormat (e.g. 2025-Q2)
• YearWeekFormat (e.g. 2006-W18)

File: hitchhiker/knowledge-base/classes/Order.vkl

import db.postgres.movie_db.public.ORDERS

kb {

    class Order {

        ...

        date Order_Date {
            name.en: "Order Date"

            sql: "${ORDERS.order_date}"

            datetime_format: DayFormat
        }
    }
}

Better Explanation

Veezoo will explain dates by default as for example "Signup Date of User in 2020", which can be clunky at times.

event_date

Often, the specific date is not required. In these cases, use event_date: Date, to get for example "Order in 2020" instead of "Order Date of Order in 2020". This is most appropriate for classes that model an event, like an order.

kb {

    class Order {

        event_date: Order_Date

        date Order_Date {
            ...
        }
    }
}

phrase_name

In other cases, a date might relate to an event different from a class, for example the signup date of a user. Then it can be appropriate to set e.g. phrase_name: "signed up" on the date, which will make Veezoo explain date filters as "User signed up in 2020".

kb {

    class User {
        ...

        date Signup_Date {
            phrase_name: "signed up"
        }
    }
}

For snapshot dates, it can also be appropriate to use an adjective here, e.g. "active" to get "Subscription active by the end of 2020".