Date and time functions

Note, that CURTIME(), UTC_TIME(), UTC_TIMESTAMP(), and TIMEDIFF() can be promoted to numeric types using arbitrary conversion functions such as BIGINT(), DOUBLE(), etc.

NOW()

Returns the current timestamp as an INTEGER.

‹›
  • SQL
SQL
📋
select NOW();
‹›
Response
+------------+
| NOW()      |
+------------+
| 1615788407 |
+------------+

CURTIME()

Returns the current time in the local timezone in hh:ii:ss format.

‹›
  • SQL
SQL
📋
select CURTIME();
‹›
Response
+-----------+
| CURTIME() |
+-----------+
| 07:06:30  |
+-----------+

CURDATE()

Returns the current date in the local timezone in YYYY-MM-DD format.

‹›
  • SQL
SQL
📋
select curdate();
‹›
Response
+------------+
| curdate()  |
+------------+
| 2023-08-02 |
+------------+

UTC_TIME()

Returns the current time in UTC timezone in hh:ii:ss format.

‹›
  • SQL
SQL
📋
select UTC_TIME();
‹›
Response
+------------+
| UTC_TIME() |
+------------+
| 06:06:18   |
+------------+

UTC_TIMESTAMP()

Returns the current time in UTC timezone in YYYY-MM-DD hh:ii:ss format.

‹›
  • SQL
SQL
📋
select UTC_TIMESTAMP();
‹›
Response
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2021-03-15 06:06:03 |
+---------------------+

SECOND()

Returns the integer second (in 0..59 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select second(now());
‹›
Response
+---------------+
| second(now()) |
+---------------+
| 52            |
+---------------+

MINUTE()

Returns the integer minute (in 0..59 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select minute(now());
‹›
Response
+---------------+
| minute(now()) |
+---------------+
| 5             |
+---------------+

HOUR()

Returns the integer hour (in 0..23 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select hour(now());
‹›
Response
+-------------+
| hour(now()) |
+-------------+
| 7           |
+-------------+

DAY()

Returns the integer day of the month (in 1..31 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select day(now());
‹›
Response
+------------+
| day(now()) |
+------------+
| 15         |
+------------+

MONTH()

Returns the integer month (in 1..12 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select month(now());
‹›
Response
+--------------+
| month(now()) |
+--------------+
| 3            |
+--------------+

QUARTER()

Returns the integer quarter of the year (in 1..4 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select quarter(now());
‹›
Response
+----------------+
| quarter(now()) |
+----------------+
| 2              |
+----------------+

YEAR()

Returns the integer year (in 1969..2038 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select year(now());
‹›
Response
+-------------+
| year(now()) |
+-------------+
| 2024        |
+-------------+

DAYNAME()

Returns the weekday name for a given timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select dayname(now());
‹›
Response
+----------------+
| dayname(now()) |
+----------------+
| Wednesday      |
+----------------+

MONTHNAME()

Returns the name of the month for a given timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select monthname(now());
‹›
Response
+------------------+
| monthname(now()) |
+------------------+
| August           |
+------------------+

DAYOFWEEK()

Returns the integer weekday index (in 1..7 range) for a given timestamp argument, according to the current timezone. Note that the week starts on Sunday.

‹›
  • SQL
SQL
📋
select dayofweek(now());
‹›
Response
+------------------+
| dayofweek(now()) |
+------------------+
| 5                |
+------------------+

DAYOFYEAR()

Returns the integer day of the year (in 1..366 range) for a given timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select dayofyear(now());
‹›
Response
+------------------+
| dayofyear(now()) |
+------------------+
|              214 |
+------------------+

YEARWEEK()

Returns the integer year and the day code of the first day of current week (in 1969001..2038366 range) for a given timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select yearweek(now());
‹›
Response
+-----------------+
| yearweek(now()) |
+-----------------+
|         2023211 |
+-----------------+

YEARMONTH()

Returns the integer year and month code (in 196912..203801 range) from a timestamp argument, according to the current timezone.

‹›
  • SQL
SQL
📋
select yearmonth(now());
‹›
Response
+------------------+
| yearmonth(now()) |
+------------------+
| 202103           |
+------------------+

YEARMONTHDAY()

Returns the integer year, month, and date code (ranging from 19691231 to 20380119) based on the current timezone.

‹›
  • SQL
SQL
📋
select yearmonthday(now());
‹›
Response
+---------------------+
| yearmonthday(now()) |
+---------------------+
| 20210315            |
+---------------------+

TIMEDIFF()

Calculates the difference between two timestamps in the format hh:ii:ss.

‹›
  • SQL
SQL
📋
select timediff(1615787586, 1613787583);
‹›
Response
+----------------------------------+
| timediff(1615787586, 1613787583) |
+----------------------------------+
| 555:33:23                        |
+----------------------------------+

DATEDIFF()

Calculates the number of days between two given timestamps.

‹›
  • SQL
SQL
📋
select datediff(1615787586, 1613787583);
‹›
Response
+----------------------------------+
| datediff(1615787586, 1613787583) |
+----------------------------------+
|                               23 |
+----------------------------------+

DATE()

Formats the date part from a timestamp argument as a string in YYYY-MM-DD format.

‹›
  • SQL
SQL
📋
select date(now());
‹›
Response
+-------------+
| date(now()) |
+-------------+
| 2023-08-02  |
+-------------+

TIME()

Formats the time part from a timestamp argument as a string in HH:MM:SS format.

‹›
  • SQL
SQL
📋
select time(now());
‹›
Response
+-------------+
| time(now()) |
+-------------+
| 15:21:27    |
+-------------+

DATE_FORMAT()

Returns a formatted string based on the provided date and format arguments. The format argument uses the same specifiers as the strftime function. For convenience, here are some common format specifiers:

  • %Y - Four-digit year
  • %m - Two-digit month (01-12)
  • %d - Two-digit day of the month (01-31)
  • %H - Two-digit hour (00-23)
  • %M - Two-digit minute (00-59)
  • %S - Two-digit second (00-59)
  • %T - Time in 24-hour format (%H:%M:%S)

Note that this is not a complete list of the specifiers. Please consult the documentation for strftime() for your operating system to get the full list.

‹›
  • SQL
SQL
📋
SELECT DATE_FORMAT(NOW(), 'year %Y and time %T');
‹›
Response
+------------------------------------------+
| DATE_FORMAT(NOW(), 'year %Y and time %T') |
+------------------------------------------+
| year 2023 and time 11:54:52              |
+------------------------------------------+

This example formats the current date and time, displaying the four-digit year and the time in 24-hour format.

DATE_HISTOGRAM()

DATE_HISTOGRAM(expr, {calendar_interval='unit_name'}) Takes a bucket size as a unit name and returns the bucket number for the value. Values are rounded to the closest bucket. The key function is:

key_of_the_bucket = interval * floor ( value / interval )

Intervals can be specified using a unit name, like week, or as a single unit, such as 1M. However, multiple units, like 2d, are not supported with calendar_interval but are allowed with fixed_interval.

The valid intervals for calendar_interval are:

  • minute, 1m
  • hour, 1h
  • day, 1d
  • week, 1w (a week is the interval between the start day of the week, hour, minute, second and the next week but the same day and time of the week)
  • month, 1M
  • year, 1y (a year is the interval between the start day of the month, time and the next year but the same day of the month, time)

The valid intervals for fixed_interval are:

  • minute, 2m
  • hour, 3h
  • day, 5d

Used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
DATE_HISTOGRAM(tm, {calendar_interval='month'}) AS months
FROM facets
GROUP BY months ORDER BY months ASC;

DATE_RANGE()

DATE_RANGE(expr, {range_from='date_math', range_to='date_math'}) takes a set of ranges and returns the bucket number for the value. The expression includes the range_from value and excludes the range_to value for each range. The range can be open - having only the range_from or only the range_to value. The difference between this and the RANGE() function is that the range_from and range_to values can be expressed in Date math expressions.

Used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
DATE_RANGE(tm, {range_to='2017||+2M/M'},{range_from='2017||+2M/M',range_to='2017||+5M/M'},{range_from='2017||+5M/M'}) AS points
FROM idx_dates
GROUP BY points ORDER BY points ASC;
Date math

Date math lets you work with dates and times directly in your searches. It's especially useful for handling data that changes over time. With date math, you can easily do things like find entries from a certain period, analyze data trends, or manage when information should be removed. It simplifies working with dates by letting you add or subtract time from a given date, round dates to the nearest time unit, and more, all within your search queries.

To use date math, you start with a base date, which can be:

  • now for the current date and time,
  • or a specific date string ending with ||.

Then, you can modify this date with operations like:

  • +1y to add one year,
  • -1h to subtract one hour,
  • /m to round to the nearest month.

You can use these units in your operations:

  • s for seconds,
  • m for minutes,
  • h (or H) for hours,
  • d for days,
  • w for weeks,
  • M for months,
  • y for years.

Here are some examples of how you might use date math:

  • now+4h means four hours from now.
  • now-2d/d is the time two days ago, rounded to the nearest day.
  • 2010-04-20||+2M/d is June 20, 2010, rounded to the nearest day.