Skip to content

Datetime Functions

In Mythradon, date and date-time values are formatted as strings in the YYYY-MM-DD HH:mm format, for example, '2021-01-01' or '2021-01-01 10:00'.

Datetime data types are stored in the database in either Coordinated Universal Time (UTC) or Greenwich Mean Time (GMT). When these datetime values are accessed through the API or displayed in the user interface, they are converted to match the timezone offset of the current user. However, when datetime values are used in formula functions, they are presented in UTC/GMT time.

This means that all datetime functions are performed in UTC/GMT time. When such a function is applied to a database record and then retrieved via the user interface or an API, the displayed value will be adjusted according to the user's timezone settings.

Mythradon supports the following datetime functions:

datetime\addDays( )

The datetime\addDays() function is used to add DAYS to a date or datetime VALUE. This will return a new date or datetime. DAYS can be negative if you need to subtract date from a date or datetime VALUE.

datetime\addDays()
datetime\addDays(VALUE, DAYS)

Example:

datetime\addDays() Example
output\printLine( datetime\addDays( '2024-04-14 03:45', 10 ) );  // Prints 2024-04-24 03:45:00

datetime\addHours( )

The datetime\addHours() function increases or decreases the datetime value by a specified number of hours. The hours parameter can be either positive or negative. It returns a modified string representing the adjusted datetime value.

datetime\addHours()
datetime\addHours(VALUE, HOURS)

Example:

datetime\addHours()
output\printLine( datetime\addHours( '2024-04-14 03:45', 10 ) );  // Prints 2024-04-14 13:45:00

datetime\addMinutes( )

The datetime\addMinutes() function increments or decrements the datetime value by a specified number of minutes. The minutes parameter can be either positive or negative. It returns a modified string representing the adjusted datetime value.

datetime\addMinutes()
datetime\addMinutes(VALUE, MINUTES)

Example:

datetime\addMinutes() Example
output\printLine( datetime\addMinutes( '2024-04-14 03:45', 10 ) );  // Prints 2024-04-14 03:55:00

datetime\addMonths( )

The datetime\addMonths() function adds or subtracts months to a date or datetime value. The months parameter can be positive or negative. It returns a modified string representing the adjusted date or datetime value.

datetime\addMonths()
datetime\addMonths(VALUE, MONTHS)

Example:

datetime\addMonths() Example
output\printLine( datetime\addMonths( '2024-04-14 03:45', 10 ) );  // Prints 2025-02-14 03:45:00

datetime\addWeeks( )

The datetime\addWeeks() function adds WEEKS to date or datetime VALUE. WEEKS can be negative. Returns a modified STRING value.

datetime\addWeeks()
datetime\addWeeks(VALUE, WEEKS)

Example:

datetime\addWeeks() Example
output\printLine( datetime\addWeeks( '2024-04-14 03:45', 10 ) );  // Prints 2024-06-23 03:45:00

datetime\addYears( )

The datetime\addYears() function adds or subtracts years to a date or datetime value. The years parameter can be positive or negative. It returns a modified string representing the adjusted date or datetime value.

datetime\addYears()
datetime\addYears(VALUE, YEARS)

Example:

datetime\addYears()
output\printLine( datetime\addYears( '2024-04-14 03:45', 10 ) );  // Prints 2034-04-14 03:45:00

datetime\closest( )

datetime\closest()
datetime\closest(VALUE, TYPE, TARGET, IS_PAST, TIMEZONE)

--

The datetime\closest() function returns the closest date or datetime to the specified VALUE, based on the provided parameters.

TYPE: Defines the unit of comparison. Accepted values include:

  • 'time'
  • 'minute'
  • 'hour'
  • 'date'
  • 'month'
  • 'dayOfWeek'

TARGET: The specific value to match against, provided as an integer or string (depending on TYPE).

IS_PAST (boolean): If true, the function searches for the closest matching value in the past. If false, it searches into the future.

TIMEZONE (optional): The timezone to apply when calculating the result. If omitted, the system's default timezone is used.

This function is useful for scheduling, recurrence calculations, and aligning timestamps to the nearest logical boundary.

Example:

datetime\closest() Example 1
datetime\closest(datetime\now(), 'time', '20:00'); // Returns the closest datetime value in the future with 20:00 time.
datetime\closest() Example 2
datetime\closest('2022-11-20', 'date', 1, true); // Returns `2022-11-01`, the first day of the month.
datetime\closest() Example 3
datetime\closest('2022-11-20', 'date', 1, false); // Returns `2022-12-01`, the first day of the next month.
datetime\closest() Example 4
datetime\closest(datetime\now(), 'dayOfWeek', 1, , 'UTC'); // Returns the next Monday (the beginning of the day UTC time).

datetime\date( )

The datetime\date() function retrieves the day of the month (ranging from 1 to 31) from a given date or datetime value. It returns 0 if the VALUE is empty. If no TIMEZONE is specified, the system's default timezone is applied.

datetime\date()
datetime\date(VALUE, [TIMEZONE])

Example:

datetime\date() Example
output\printLine( datetime\date( '2024-04-24' ) );  // Prints 24

datetime\dayOfWeek( )

The datetime\dayOfWeek() function retrieves the day of the week (ranging from 0 to 6), with -1 returned if the VALUE is empty. Sunday is represented by 0. If no TIMEZONE is specified, the system's default timezone is utilised.

datetime\dayOfWeek()
datetime\dayOfWeek(VALUE, [TIMEZONE])

Examples:

datetime\dayOfWeek() Example 1
output\printLine( datetime\dayOfWeek( '2024-04-07', 'UTC' ) );  // Sunday - prints 0
datetime\dayOfWeek() Example 2
output\printLine( datetime\dayOfWeek( '2024-04-04', 'UTC' ) );  // Thursday - prints 4

datetime\diff( )

The datetime\diff() function calculates the difference between two dates or datetimes based on the specified INTERVAL_TYPE, which can be 'years', 'months', 'days', 'hours', or 'minutes'. It returns null in case of an error. The result is negative if VALUE_1 is less than VALUE_2.

datetime\diff()
datetime\diff(VALUE_1, VALUE_2, INTERVAL_TYPE)

Examples:

datetime\diff() Example 1
output\printLine( datetime\diff('2024-04-17', '2024-04-01', 'days') );  // Prints 16
datetime\diff() Example 2
output\printLine( datetime\diff('2024-04-16', '1970-10-07', 'days') );  // Prints 19550
datetime\diff() Example 3
output\printLine( datetime\diff('2024-04-18 01:00', '2024-04-17 00:00', 'hours') );  // Prints 25

datetime\format( )

The datetime\format() function transforms a date or datetime VALUE into a string formatted according to the specified timezone and format, or based on the application's default settings. Both TIMEZONE and FORMAT parameters are optional. If TIMEZONE is omitted, the default timezone is applied. If FORMAT is omitted, the default format is used.

datetime\format()
datetime\format(VALUE, [TIMEZONE], [FORMAT])

Examples:

datetime\format() Example 1
datetime\format(closeDate, 'America/New_York', 'MM/DD/YYYY')
datetime\format() Example 2
datetime\format(dateStart, 'America/New_York', 'MM/DD/YYYY hh:mma')
datetime\format() Example 3
datetime\format(dateStart, 'Europe/Amsterdam', 'DD/MM/YYYY HH:mm')

Date and time fields can be formatted in a variety of ways using the following formatting options:

  • YYYY - 4 digit year: 2023
  • YY - 2 digit year: 23
  • M - Single digit month: 1..12
  • MM - 2 digit month number: 01..12
  • MMM - Short month name: Jan..Dec
  • MMMM - Full month name: January..December
  • D - Single digit day of month: 1..31
  • DD - 2 digit day of month: 01..31
  • Do - Day of month with ordinal: 1st..31st
  • ddd - Short day of week name: Tue
  • dddd - Full day of week name: Tuesday
  • H - Single digit hour (24 hour time): 0..23
  • HH - 2 digit hour (24 hour time): 00..23
  • h - Single digit hour (12 hour time): 0..12
  • hh - 2 digit hour (12 hour time): 00..12
  • a A - post or ante meridiem: am PM
  • m - Single digit minutes: 0..59
  • mm - 2 digit minutes: 00..59
  • s - Single digit seconds: 0..59
  • ss - 2 digit seconds: 00..59
  • Z ZZ zz - offset from UTC : +02:00 +0200
  • zz - timezone: Europe/London

datetime\hour( )

The datetime\hour() function retrieves the hour from a datetime VALUE, expressed in a 24-hour format (ranging from 0 to 23). It returns -1 if the VALUE is empty. If the TIMEZONE parameter is omitted, the function defaults to using the system's timezone.

datetime\hour()
datetime\hour(VALUE, [TIMEZONE])

Example:

datetime\hour() Example
output\printLine( datetime\hour('2024-04-16 15:45', 'UTC') );  // Prints 15

datetime\minute( )

The datetime\minute() function extracts the minutes (0-59) from a datetime VALUE. It returns -1 if the VALUE is empty. If no TIMEZONE is specified, the function defaults to the system's timezone.

datetime\minute()
datetime\minute(VALUE, [TIMEZONE])

Example:

datetime\minute() Example
output\printLine( datetime\minute('2024-04-16 15:45', 'UTC') );  // Prints 45

datetime\month( )

The datetime\month() function returns the month (ranging from 1 to 12) from a datetime VALUE. If the VALUE is empty, it returns 0. If the TIMEZONE parameter is omitted, the system's default timezone is applied.

datetime\month()
datetime\month(VALUE, [TIMEZONE])

Example:

datetime\month() Example
output\printLine( datetime\month('2024-04-16 15:45', 'UTC') );  // Prints 4

datetime\now( )

The datetime\now() function retrieves the current system datetime in the format 'YYYY-MM-DD HH🇲🇲ss'.

Note

The datetime\now() function will always return the date/time in GMT/UTF time regardless of your timezone setting.

datetime\now()
datetime\now()

Example:

datetime\now() Example
output\printLine( datetime\now() );  // Prints the current datetime. I.e. 2024-04-16 02:07:56

datetime\today( )

The datetime\today() function returns the current date in the 'YYYY-MM-DD' format, excluding the time component.

datetime\today()
datetime\today()

Example:

datetime\today()
output\printLine( datetime\today() );  // Prints the current date. I.e. 2024-04-16

datetime\year( )

The datetime\year() function returns the year from a datetime VALUE. If the VALUE is empty, it returns 0. If the TIMEZONE parameter is omitted, the system's default timezone is applied.

datetime\year()
datetime\year(VALUE, [TIMEZONE])

Example:

datetime\year() Example
output\printLine( datetime\year( '2024-04-14 03:45' ) );  // Prints 2024

See Also