Date Math

Working with date and time data types in programming languages, whether this is converting from timestamps to human readable formats, adjusting for different time zones, or handling intervals and durations is brought with tedious conversions and edge cases in both SQL and general purpose programming languages.

Let us consider a topic called subscriptions. This topic has details about customers who have subscribed to some service. Each record contains a subscription_date field which is a timestamp of when the subscription started and a name field which is the customer name. As a simple query, we want to see all customers with a renewal date, which shall be one month from the subscription date.

How to calculate the renewal date? A naive implementation might be to add (1000 * 60 * 60 * 24 * 31) milliseconds to the subscription_date. But what is a month - a month can have 28 days, 30 days or 31 days (in the Gregorian calendar). In daylight savings timezones, a month may include an extra hour or might miss an hour. It is hard to handle the uneven nature of date units in a query (in this example, the renewal date may have been pre-calculated as part of the record, but for ad-hoc querying, you can not store every possible date with the record).

Lenses offers direct support for easier manipulation of dates, times and timestamps. We call this unique functionality DateMath expressions. DateMath expressions can be used in any SQL statement where a general purpose expression is allowed - for example as part of a where filter, in a group by clause, a select projection, and so on.

Syntax

Every DateMath expression starts with a base date or time followed by the addition or subtraction of one or more durations.

The base date or time (from here onwards) is derived from a field in a table or a function such as now() or yesterday() that generates datetime values (See the tabled_functions page for a full list of datetime functions).

Duration operations translate the base date by either adding or subtracting a duration defined using the special DateMath shorthand - this is where the math part of the DateMath name comes from. These operations are designed to be both extremely simple to use and also handle the rollover of units that we discussed in our example earlier.

The shorthand syntax is a unit value followed by a unit symbol. The symbols are as follows:

  • y (year)
  • M (month)
  • w (week)
  • d (day)
  • h (hour)
  • m (minute)
  • s (second)

For example, 60s would constitute 60 seconds, which is equivalent to 1m, and 48h would be equivalent to 2d.

So, a full DateMath expression would look like the following examples:

now() + 1d
customers.age + 1y
orders.delivery.date + 1d - 1h

Returning to our earlier example with subscriptions and renewal dates, the solution is elegantly handled by the following query:

select subscription_date + 1M as renewal from subscriptions

The subscription_date is used as the base, a duration of one month is added to the base, and then this field is aliased with the name renewal. The DateMath expression will automatically handle daylight savings, the number of days in a month, timezone differences, rolling into a new year and so on.

We may decide to give every customer an extra day free on their renewal, as a grace period. So the query can be simply tweaked by adding another duration as follows:

select subscription_date + 1M + 1d as renewal from subscriptions

As mentioned earlier, the duration operands can be chained with no upper limit on the number of allowed operations.

Base Date Functions

Normally the base would come from a field, but sometimes the base date may needs to be modified - for example extracting the minutes component only from a time value. In addition to this, the base can be generated using a function unrelated to any field.

Name Description Usage
NOW Returns the current datetime as a timestamp now()
YESTERDAY Returns the current datetime as a timestamp minus one day handling daylight savings yesterday()
TOMORROW Returns the current datetime as a timestamp plus one day handling daylight savings tomorrow()
LASTWEEK Returns the current datetime as a timestamp minus one week handling daylight savings lastweek()
NEXTWEEK Returns the current datetime as a timestamp plus one week handling daylight savings nextweek()
LASTMONTH Returns the current datetime as a timestamp minus one month handling daylight savings and varying length of months lastmonth()
NEXTMONTH Returns the current datetime as a timestamp plus one month handling daylight savings and varying length of months nextmonth()
from_datetime Converts an ISO formatted date into a timestamp value. The single argument must be a string expression (eg from a field) that represents a date in the standard ISO format, eg 2011-12-03T10:15:30 from_datetime(expr)
epoch_to_datetime Converts a date into a timestamp. The first argument must be a string expression (eg from a field), and the second must be a string pattern which defines the format to be used epoch_to_datetime(expr, 'dd-MM-yyyy')

Supported Types

The basic types supported by DateMath are:

  • Date
  • Time
  • DateTime
  • Timestamp