Axibase Time Series Database

DateTime Object

Overview

A DateTime object represents a specific date and time. The object provides fields to extract calendar units and functions to perform calendar arithmetic.

DateTime objects can be retrieved from window fields and by creating new objects using the to_datetime function. In addition, special date objects such as now are available to access the current server time.

When printed as text, a DateTime object is formatted as ISO date with additional time zone details.

${now} --> 2018-08-17T15:13:16.946Z[Etc/UTC]

To format the DateTime object using a different pattern or time zone, use the date_format function.

// Jan-09 15:23
date_format(command_time, "MMM-dd HH:mm", "US/Pacific")

To compare two dates, use the millis field.

// Check the difference between open and command times
command_time.millis - open_time.millis > 15000

To compare the date with current time, use the elapsedTime function.

// Check if any command arrived within the 5 minutes
elapsedTime(add_time) < 5*60000

To calculate the interval between the two DateTime objects, the elapsedTime function.

// Return the number of milliseconds between add_time and command_time
elapsedTime(add_time, command_time) < 5*60000

The DateTime objects can be compared in boolean conditions, for example in the notification control flow statements.

@if{command_time > add_time}
Command has future timestamp: `${command_time}` / `${add_time}` / `${now}`
@end{}

To activate the rule during specific hours of the day, use the timeOfDay field:

now.is_workday() && now.timeOfDay BETWEEN '09:30' and '16:30'

Time Zone

By default, a DateTime object is initialized in server time zone.

To change the time zone of an existing DateTime object, invoke the to_timezone(tz) function which returns a modified DateTime object in a custom time zone.

To create a new DateTime object from Unix time in milliseconds, use the to_datetime(ms, tz) function.

Fields

The table below enumerates available DateTime object fields and their values for 2018-01-13T16:45:22.303Z (Saturday).

Method Value for 2018-01-13T16:45:22.303Z
day_of_week Saturday
year 2018
monthOfYear 1
dayOfMonth 13
hourOfDay 16
minuteOfHour 45
secondOfMinute 22
millisOfSecond 303
dayOfYear 13
weekOfWeekyear 2
nanoOfDay 60322303000000L
millisOfDay 60322303
minuteOfDay 1005
secondOfDay 60322
timeOfDay 16:45:22.303
weekyear 2018
yearOfCentury 18
yearOfEra 2018
centuryOfEra 20
millis 1515861922303
Unix time in milliseconds.
epochNano 1515861922303000000L
iso 2018-01-13T16:45:22.303Z
ISO-8601 date.
next_workday DateTime('2018-01-16T00:00Z[UTC]')
previous_workday DateTime('2018-01-12T00:00Z[UTC]')
next_non_working_day DateTime('2018-01-14T00:00Z[UTC]')
previous_non_working_day DateTime('2018-01-07T00:00Z[UTC]')
is_weekday() false
is_weekday('usa') false
is_weekend() true
is_weekend('usa') true
is_workday() false
is_workday('usa') false
is_exceptionday('usa') false

Fields next_workday, previous_workday, next_non_working_day, and previous_non_working_day are calculated based on the workday calendar specified in Rule Overview settings. Default value inherits default.holiday.calendar server property.

Functions

add Function

add(number count, string unit) DateTime

Returns a DateTime object created by adding an interval to the current DateTime object. The interval is specified as count of time units.

now.add(1, 'hour')

If count is negative, the interval is subtracted from the current DateTime object.

Fractional count is rounded down to the nearest integer.

The time unit is case-insensitive and supports both singular and plural units (hour/HOUR/hours/HOURS).

The new DateTime object inherits the time zone of the original object.

Examples:

/* Returns true if tomorrow is a working day. */
now.add(1, 'day').is_workday()

/* Returns day of week 10 year ago. */
now.add(-10, 'years').day_of_week

is_weekday Function

is_weekday( [string code] ) boolean
  • Returns true if the DateTime object is a weekday.
  • Accepts optional ISO-3166 alpha-3 country code.
  • If country cannot be resolved by country code, returns true if day of week is not Saturday or Sunday.
  • If country code is not specified, the database uses the default.holiday.calendar server property.
  • By default default.holiday.calendar resolves country code from the user.country system property.

is_weekend Function

is_weekend( [string code] ) boolean
  • Returns true if the DateTime object is a weekend day.
  • Accepts optional ISO-3166 alpha-3 country code.
  • If country cannot be resolved by country code, returns true if day of week is Saturday or Sunday.
  • If country code is not specified, the database uses the default.holiday.calendar server property.
  • By default default.holiday.calendar resolves country code from the user.country system property.

is_workday Function

is_workday( [string calendarKey] ) boolean
  • Returns true if the DateTime object is a working day based on the observed workday calendar.
  • Accepts optional calendar key argument calendarKey.
  • If calendarKey is not specified, the database uses the default.holiday.calendar server property.
  • The function throws an exception if no workday calendar is found, or if the workday calendar contains no date for the given year.
// returns true if average exceeds 10 on a working day, taking observed holidays into account
avg() > 10 && is_workday('usa')

// returns true 2 days before the first non-working day, typically on Thursdays
now.hourOfDay = 12 AND
  now.is_workday()
  AND now.add(1, 'day').is_workday()
  AND NOT now.add(2, 'day').is_workday()

is_exceptionday Function

is_exceptionday( [string code] ) boolean

The convenience function returns true if a working day is classified as a holiday or if a weekend day becomes a working day.

now.is_weekend() && now.is_workday() || now.is_weekday() && !now.is_workday()

to_timezone Function

to_timezone(string zone) DateTime
  • Returns a new DateTime object based on server time but modified to the specified time zone.
now.to_timezone('Europe/Berlin').next_workday