Apache Drill SQL Date And Time Function

Apache Drill supports the following list of Date, time functions that are used to perform different types of operations such as adding two dates, checking the difference between two dates, checking the local time, checking the current time, and so on. The time function supported by Drill is based on the Gregorian calendar and the range of time would be between 1971 to 2037.

The following is the list of Apache Drill Date/Time Functions.

Drill Date/Time Functions Return Type
AGE(TIMESTAMP) INTERVALDAY or INTERVALYEAR
EXTRACT(field from time_expression) DOUBLE
CURRENT_DATE DATE
CURRENT_TIME TIME
CURRENT_TIMESTAMP TIMESTAMP
DATE_ADD DATE, TIMESTAMP
DATE_DIFF DATE, TIMESTAMP
DATE_PART DOUBLE
DATE_SUB DATE, TIMESTAMP
LOCALTIME TIME
LOCALTIMESTAMP TIMESTAMP
NOW TIMESTAMP
TIMEOFDAY VARCHAR
UNIX_TIMESTAMP BIGINT
NEARESTDATE** TIMESTAMP
TIMESTAMPADD* Inferred based on unit of time
TIMESTAMPDIFF* Inferred based on unit of time

Let us see a couple of the example o Apache Drill Date/Time Functions.


1. Apache Drill DATE_ADD Function

Drill DATE_ADD function is used to date/time and several days/hours, or it can add date/time and date/time interval.

DATE_ADD Function Syntax:

The following is the syntax of the DATE_ADD Function.

DATE_ADD(keyword literal, integer) DATE_ADD(keyword literal, interval expr) DATE_ADD(column, integer) DATE_ADD(column, interval expr)

DATE_ADD Examples

In the following example, we are adding 5 days to the date that is 2020-03-12. So after processing the result will be 2020-03-17.

Command:

apache drill> SELECT DATE_ADD(date '2020-03-12', 5) FROM (VALUES(1));

Output:

drill date add function cloudduggu


2. Apache Drill DATE_DIFF Function

Drill DATE_DIFF function is used to return the difference of a date/time and several days/hours.

DATE_DIFF Function Syntax:

The following is the syntax of the DATE_DIFF Function.

DATE_DIFF(keyword literal, integer) DATE_DIFF(keyword literal, interval expr) DATE_DIFF(column, integer) DATE_DIFF(column, interval expr)

DATE_DIFF Examples

In the following example, we are subtracting the value 5 from the date 2020-10-12.

Command:

apache drill> SELECT DATE_DIFF(date '2020-10-12', 5) FROM (VALUES(1));

Output:

drill date diff function cloudduggu


3. Apache Drill Other Date and Time Functions

The below list of Date and time functions are used to return the current date/time, local time, today's time, and so on.

  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • LOCALTIME
  • LOCALTIMESTAMP
  • NOW
  • TIMEOFDAY

Let us see the example of the above Date and Time functions.

Command:

apache drill> SELECT CURRENT_DATE FROM (VALUES(1));
apache drill> SELECT CURRENT_TIME FROM (VALUES(1));
apache drill> SELECT CURRENT_TIMESTAMP FROM (VALUES(1));
apache drill> SELECT LOCALTIME FROM (VALUES(1));
apache drill> SELECT LOCALTIMESTAMP FROM (VALUES(1));
apache drill> SELECT NOW() FROM (VALUES(1));
apache drill> SELECT TIMEOFDAY() FROM (VALUES(1));

Output:

drill date time function cloudduggu

drill date & time function cloudduggu