Time Functions

Time functions are grouped into the following categories:

Instance Functions

Expressing time relative to the execution moment.

now

now() accepts zero arguments and returns the current date time at the moments of request execution.

today, yesterday

today() and yesterday() accepts zero arguments and returns either current or yesterdays date at the moments of request execution

Conversion Functions

formatDateTime

Formats a date or date time according to the modifier pattern.

Params:

  • t: date or date time value
  • modifier: format string
  • timezone (Optional) : string

Usage:

  • formatDateTime(t, modifier)
  • formatDateTime(t, modifier, timezone)

The following list of modifier can be used in conjuction along with new-line (%n) and horizontal-tab (%t). Example based on 2018-01-02 22:33:44

ModifierDescriptionExample:
%Cyear divided by 100 and truncated to integer (00-99)18
%dday of the month, zero-padded (01-31)02
%DShort MM/DD/YY date, equivalent to %m/%d/%y01/02/18
%eday of the month, space-padded ( 1-31)2
%Fshort YYYY-MM-DD date, equivalent to %Y-%m-%d2018-01-02
%Hhour in 24h format (00-23)22
%Ihour in 12h format (01-12)10
%jday of the year (001-366)002
%mmonth as a decimal number (01-12)01
%Mminute (00-59)33
%pAM or PM designationPM
%R24-hour HH:MM time, equivalent to %H:%M22:33
%Ssecond (00-59)44
%TISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S22:33:44
%uISO 8601 weekday as number with Monday as 1 (1-7)2
%VISO 8601 week number (01-53)01
%wweekday as a decimal number with Sunday as 0 (0-6)2
%yYear, last two digits (00-99)18
%YYear2018

to Year|Month|Hour|Minute|Second|Quarter

Converts a date or date with time to a number. Year (1-366), Month (1-31), Hour (0-23), Minute (0-59), Second (0-59) and Quarter (1-4).

Params:

  • t: date or date time value
  • timezone (Optional) : string

Usage:

  • toYear(t),toYear(t,timezone)
  • toMonth(t), toMonth(t,timezone)
  • toHour(t), toHour(t,timezone)
  • toMinute(t), toMinute(t,timezone)
  • toSecond(t), toSecond(t,timezone)
  • toQuarter(t), toQuarter(t,timezone)

Returns:

  • UInt16

toDayOf Year|Month|Week

Converts a date or date with time to a number. Year (1-366), Month (1-31) or Week (1-7) where 1 in Monday.

Params:

  • t: date or date time value
  • timezone (Optional) : string

Usage:

  • toDayOfYear(t),toDayOfYear(t,timezone)
  • toDayOfMonth(t)
  • toDayOfWeek(t)

Returns:

  • UInt16 Year
  • UInt8 Month|Week

toStartOf Year|Month|Day|Hour|Minute

Rounds down a date or date with time to the start of the unit interval.

Year | Month to the first day. Hour|Minute to the start of the unit.

Params:

  • t: date or date time value
  • timezone (Optional): string

Usage:

  • toStartOfYear(t),toStartOfYear(t,timezone)
  • toStartOfMonth(t)
  • toStartOfDay(t)
  • toStartOfHour(t)
  • toStartOfMinute(t)

Returns:

  • DateTime rounded to required interval

toStartOfWeek

Rounds down a date or date with time to the start of the week. The default mode (0) rounds to a Sunday, while mode 1 is a Monday.

Params:

  • t: date or date time value
  • mode (Optional): 0 or 1
  • timezone (Optional): string

Usage:

  • toStartOfWeek(t)
  • toStartOfWeek(t, mode)
  • toStartOfWeek(t, mode, timezone)

Returns:

  • DateTime rounded to required interval

toStartOf Five|Ten|Thirteen Minute

Rounds down a date with time to the start of the five|ten|firteen minute interval.

Params:

  • t: date or date time value
  • timezone (Optional): string

Usage:

  • toStartOfFiveMinute(t),toStartOfFiveMinute(t,timezone)
  • toStartOfTenMinute(t)
  • toStartOfFifteenMinute(t)

Returns:

  • DateTime rounded to required interval

toYYYYMM

Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 100 + MM).

Params:

  • t: date or date time value
  • timezone (Optional): string

Usage:

  • toYYYYMM(t)
  • toYYYYMM(t, timezone)

Returns:

  • UInt32

toYYYYMMDD


Converts a date or date with time to a UInt32 number containing the year and month number (YYYY 10000 + MM 100 + DD)

Params:

  • t: date or date time value
  • timezone (Optional) : string

Usage:

  • toYYYYMMDD(t)
  • toYYYYMMDD(t, timezone)

Returns:

  • UInt32

toStartOfInterval

A generalization of other functions named toStartOf* for fine grain control of interval periods.

Params:

  • t: date or date time value
  • INTERVAL: fixed string
  • n: numerical value
  • unit: year|month|hour|second
  • timezone (Optional) : string

Usage:

  • toStartOfInterval(t, INTERVAL n unit)
  • toStartOfInterval(t, INTERVAL n unit, timezone)

Returns:

  • DateTime rounded to interval

toUnixTimestamp

Converts a date with time to a unix timestamp.

Params:

  • t: date or date time value
  • timezone (Optional): string

Usage:

  • toUnixTimestamp(t)
  • toUnixTimestamp(t,timezone)

Returns:

  • UInt32

toWeek

Returns the week number for date or datetime. The optional mode flag controls whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. Default 0. See reference

Params:

  • t: date or date time value
  • mode (Optional): int in range 0..9, default is 0
  • timezone (Optional): string

Usage:

  • toWeek(t)
  • toWeek(t, mode)
  • toWeek(t, mode, timezone)

Returns:

  • UInt8 week number

Date Math Functions

add Years|Months|Weeks|Hours|Minutes|Seconds|Quarters

Adds interval to a Date/DateTime

Params:

  • t: date or date time value
  • n: numerical value
  • timezone (Optional): string

Usage:

  • addYears(t, n), addYears(t, n, timezone)
  • addMonths(t, n)
  • addQuarters(t, n)
  • addWeeks(t, n)
  • addDays(t, n)
  • addMinutes(t, n)
  • addSeconds(t, n)

Returns:

  • DateTime

dateDiff

Returns the difference between two times expressed in 'unit'.

Params:

  • unit: string year|month|week|day|hour|minute|second|quarter
  • start: date or date time value
  • end: date or date time value
  • timezone (Optional): string

Usage:

  • dateDiff(unit, start, end)
  • dateDiff(unit, start, end, timezone)

Returns:

  • Int the difference in time based on unit

subtract Years|Months|Weeks|Hours|Minutes|Seconds|Quarters

Subtracts a time interval from a Date or DateTime.

Params:

  • t: date or date time value
  • n: numerical value
  • timezone (Optional): string

Usage:

  • subtractYears(t, n), subtractYears(t, n, timezone)
  • subtractMonths(t, n)
  • subtractQuarters(t, n)
  • subtractWeeks(t, n)
  • subtractDays(t, n)
  • subtractMinutes(t, n)
  • subtractSeconds(t, n)

Returns:

  • DateTime

Did this page help you?