Skip to main content
Version: Latest

Date functions

This cheat sheet provides a quick reference guide for various date functions commonly used in data analysis and programming. Each function is accompanied by its syntax, a sample usage, and a brief description.

DATETIME_DIFF​

The DATETIME_DIFF function calculates the difference between two dates in various units.

Syntax​

DATETIME_DIFF(date1, date2, ["milliseconds" | "ms" | "seconds" | "s" | "minutes" | "m" | "hours" | "h" | "days" | "d" | "weeks" | "w" | "months" | "M" | "quarters" | "Q" | "years" | "y"])

Sample​

DATETIME_DIFF("2022/10/14", "2022/10/15", "seconds") => -86400

Remark​

This function compares two dates and returns the difference in the specified unit. Positive integers indicate that second date is in the past compared to first, and vice versa for negative values.

DATEADD​

The DATEADD function adds a specified value to a date or datetime.

Syntax​

DATEADD(date | datetime, value, ["day" | "week" | "month" | "year"])

Sample​

DATEADD('2022-03-14', 1, 'day')     => 2022-03-15
DATEADD('2022-03-14', 1, 'week')    => 2022-03-21
DATEADD('2022-03-14', 1, 'month')   => 2022-04-14
DATEADD('2022-03-14', 1, 'year')    => 2023-03-14

Conditional Example​

IF(NOW() < DATEADD(date, 10, 'day'), "true", "false") => If the current date is less than the specified date plus 10 days, it returns true. Otherwise, it returns false.

Remark​

This function supports date and datetime fields and can handle negative values.

NOW​

The NOW function returns the current time and day.

Syntax​

NOW()

Sample​

NOW() => 2022-05-19 17:20:43 (current date & time)

Conditional Example​

IF(NOW() < date, "true", "false") => If the current date is less than the specified date, it returns true. Otherwise, it returns false.

Remark​

This function provides the current time and day, supporting datetime fields and negative values.

WEEKDAY​

The WEEKDAY function returns the day of the week as an integer.

Syntax​

WEEKDAY(date, [startDayOfWeek])

Sample​

WEEKDAY(NOW()) => If today is Monday, it returns 0.
WEEKDAY(NOW(), "sunday") => If today is Monday, it returns 1.

Remark​

Returns the day of the week as an integer between 0 and 6 (inclusive), with Monday as the default start day. The start day of the week can be optionally changed by specifying it as the second argument.