Impala date and time functions
The underlying Impala data type for date and time data is
TIMESTAMP
and DATE
.
‑‑use_local_tz_for_unix_timestamp_conversions
startup flag for the impalad daemon. - This setting is off by default, meaning that functions such as
FROM_UNIXTIME()
andUNIX_TIMESTAMP()
consider the input values to always represent the UTC time zone. - This setting also applies when you
CAST()
aBIGINT
value toTIMESTAMP
, or aTIMESTAMP
value toBIGINT
. When this setting is enabled, these functions and operations convert to and from values representing the local time zone. See TIMESTAMP data type for details about how Impala handles time zone considerations for theTIMESTAMP
data type.
Function reference:
Impala supports the following data and time functions:
- ADD_MONTHS
- ADDDATE
- CURRENT_TIMESTAMP
- DATE_ADD
- DATE_PART
- DATE_SUB
- DATE_TRUNC
- DATEDIFF
- DAY
- DAYNAME
- DAYOFWEEK
- DAYOFYEAR
- DAYS_ADD
- DAYS_SUB
- EXTRACT
- FROM_TIMESTAMP
- FROM_UNIXTIME
- FROM_UTC_TIMESTAMP
- HOUR
- HOURS_ADD
- HOURS_SUB
- INT_MONTHS_BETWEEN
- MICROSECONDS_ADD
- MICROSECONDS_SUB
- MILLISECOND
- MILLISECONDS_ADD
- MILLISECONDS_SUB
- MINUTE
- MINUTES_ADD
- MINUTES_SUB
- MONTH
- MONTHNAME
- MONTHS_ADD
- MONTHS_BETWEEN
- MONTHS_SUB
- NANOSECONDS_ADD
- NANOSECONDS_SUB
- NEXT_DAY
- NOW
- QUARTER
- SECOND
- SECONDS_ADD
- SECONDS_SUB
- SUBDATE
- TIMEOFDAY
- TIMESTAMP_CMP
- TO_DATE
- TO_TIMESTAMP
- TO_UTC_TIMESTAMP
- TRUNC
- UNIX_TIMESTAMP
- UTC_TIMESTAMP
- WEEKOFYEAR
- WEEKS_ADD
- WEEKS_SUB
- YEAR
- YEARS_ADD
- YEARS_SUB
- ADD_MONTHS(TIMESTAMP / DATE date, INT months), ADD_MONTHS(TIMESTAMP / DATE date, BIGINT months)
-
Purpose: Adds months to
date and returns the new date value. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
Usage notes:
Same as
MONTHS_ADD()
. Available in Impala 1.4 and higher. For compatibility when porting code with vendor extensions. - If date is
- ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days)
-
Purpose: Adds days to
date and returns the new date value.
The days value can be negative, which gives the same result as the
SUBDATE()
function.Return type:- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
- If date is
- CURRENT_DATE()
-
Purpose: Returns the current date.
Any references to the
CURRENT_DATE()
function are evaluated at the start of a query. All calls toCURRENT_DATE()
within the same query return the same value, and the value does not depend on how long the query takes.Return type:
DATE
- CURRENT_TIMESTAMP()
-
Purpose: Alias for the
NOW()
function.Return type:
TIMESTAMP
- DATE_ADD(TIMESTAMP / DATE date, INT / BIGINT days), DATE_ADD(TIMESTAMP / DATE date, interval_expression)
- Purpose: Adds a specified number of days to the date argument.
- DATE_CMP(DATE date1, DATE date2)
-
Purpose: Compares date1 and
date2 and returns:
-
0
if the dates are identical. - 1 if date1 > date2.
- -1 if date1 < date2.
-
NULL
if date1 or date2 isNULL
.
Return type:
INT
-
- DATE_PART(STRING part, TIMESTAMP / DATE date)
-
Purpose: Similar to
EXTRACT()
, with the argument order reversed. Supports the same date and time units asEXTRACT()
. For compatibility with SQL code containing vendor extensions.Return type:
BIGINT
- DATE_SUB(TIMESTAMP startdate, INT days), DATE_SUB(TIMESTAMP startdate, interval_expression)
-
Purpose: Subtracts a specified number of days from a
TIMESTAMP
value. With anINTERVAL
expression as the second argument, you can calculate a delta value using other units such as weeks, years, hours, seconds, and so on.Return type:- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
Examples:
The following example shows the simplest usage, of subtracting a specified number of days from a
TIMESTAMP
value:select now() as right_now, date_sub(now(), 7) as last_week; +-------------------------------+-------------------------------+ | right_now | last_week | +-------------------------------+-------------------------------+ | 2016-05-20 11:21:30.491011000 | 2016-05-13 11:21:30.491011000 | +-------------------------------+-------------------------------+
The following examples show the shorthand notation of an
INTERVAL
expression, instead of specifying the precise number of days. TheINTERVAL
notation also lets you work with units smaller than a single day.select now() as right_now, date_sub(now(), interval 3 weeks) as 3_weeks_ago; +-------------------------------+-------------------------------+ | right_now | 3_weeks_ago | +-------------------------------+-------------------------------+ | 2016-05-20 11:23:05.176953000 | 2016-04-29 11:23:05.176953000 | +-------------------------------+-------------------------------+ select now() as right_now, date_sub(now(), interval 6 hours) as 6_hours_ago; +-------------------------------+-------------------------------+ | right_now | 6_hours_ago | +-------------------------------+-------------------------------+ | 2016-05-20 11:23:35.439631000 | 2016-05-20 05:23:35.439631000 | +-------------------------------+-------------------------------+
Like all date/time functions that deal with months,
date_add()
handles nonexistent dates past the end of a month by setting the date to the last day of the month. The following example shows how the nonexistent date April 31st is normalized to April 30th:select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st'; +---------------------+ | april_31st | +---------------------+ | 2016-04-30 00:00:00 | +---------------------+
- If date is
- DATE_TRUNC(STRING unit, TIMESTAMP / DATE ts)
-
Purpose: Returns the ts value truncated to
the specified unit.
Argument: The
unit
argument is not case-sensitive. This argument string can be one of:Unit Supported for TIMESTAMP Supported for DATE 'MICROSECONDS'
Yes No 'MILLISECONDS'
Yes No 'SECOND'
Yes No 'MINUTE'
Yes No 'HOUR'
Yes No 'DAY'
Yes Yes 'WEEK'
Yes Yes 'MONTH'
Yes Yes 'YEAR'
Yes Yes 'DECADE'
Yes Yes 'CENTURY'
Yes Yes 'MILLENNIUM'
Yes Yes Usage notes:
Although this function is similar to calling
TRUNC()
with aTIMESTAMP
orDATE
argument, the order of arguments and the recognized units are different betweenTRUNC()
andDATE_TRUNC()
. Therefore, these functions are not interchangeable.This function is typically used in
GROUP BY
queries to aggregate results from the same hour, day, week, month, quarter, and so on. You can also use this function in anINSERT ... SELECT
into a partitioned table to divideTIMESTAMP
orDATE
values into the correct partition.Return type:-
TIMESTAMP
if the second argument, ts, isTIMESTAMP
. -
DATE
if the second argument, ts, isDATE
.
Examples:
DATE_TRUNC('HOUR', NOW())
returns2017-12-05 13:00:00
.DATE_TRUNC('MILLENNIUM', DATE'2019-08-02')
returns2001-01-01
. -
- DATEDIFF(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate)
-
Purpose: Returns the number of days from
startdate to enddate.
If enddate > startdate, the return value is positive.
If enddate < startdate, the return value is negative.
If enddate = startdate, the return value is zero.
Return type:
INT
Usage notes:
The time portions of the enddate and startdate values are ignored. For example, 11:59 PM on one day and 12:01 AM on the next day represent a
DATEDIFF()
of -1 because the date/time values represent different days, even though theTIMESTAMP
values differ by only 2 minutes. - DAY(TIMESTAMP / DATE date), DAYOFMONTH(TIMESTAMP / DATE date)
-
Purpose: Returns the day value from the
date argument. The value represents the day of
the month, therefore is in the range 1-31, or less for months
without 31 days.
Returns
NULL
for nonexistent dates, e.g.Feb 30
, or misformatted date strings, e.g.'1999-02-013'
.Return type:
INT
- DAYNAME(TIMESTAMP / DATE date)
-
Purpose: Returns the day name of the date
argument. The range of return values is
'Sunday'
to'Saturday'
. Used in report-generating queries, as an alternative to callingDAYOFWEEK()
and turning that numeric return value into a string using aCASE
expression.Return type:
STRING
- DAYOFWEEK(TIMESTAMP / DATE date)
-
Purpose: Returns the day field of the date
arguement, corresponding to the day of the week. The range of return
values is 1 (Sunday) to 7 (Saturday).
Return type:
INT
- DAYOFYEAR(TIMESTAMP / DATE date)
-
Purpose: Returns the day field from the
date argument, corresponding to the day of the
year. The range of return values is 1 (January 1) to 366 (December
31 of a leap year).
Return type:
INT
- DAYS_ADD(TIMESTAMP / DATE date, INT / BIGINT days)
-
Purpose: Returns the value with the number of
days added to date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
- If date is
- DAYS_SUB(TIMESTAMP / DATE date, INT / BIGINT days)
-
Purpose: Returns the value with the number of
days subtracted from date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
- If date is
- EXTRACT(TIMESTAMP / DATE ts, STRING unit), EXTRACT(unit FROM TIMESTAMP / DATE ts)
-
Purpose: Returns one of the numeric date or time fields,
specified by unit, from ts.
Argument: The
unit
argument value is not case-sensitive. Theunit
string can be one of:Unit Supported for TIMESTAMP ts Supported for DATE ts 'EPOCH'
Yes No 'MILLISECOND'
Yes No 'SECOND'
Yes No 'MINUTE'
Yes No 'HOUR'
Yes No 'DAY'
Yes Yes 'MONTH'
Yes Yes 'QUARTER'
Yes Yes 'YEAR'
Yes Yes Usage notes:
Typically used in
GROUP BY
queries to arrange results by hour, day, month, and so on. You can also use this function in anINSERT ... SELECT
statement to insert into a partitioned table to split upTIMESTAMP
values into individual parts, if the partitioned table has separate partition key columns representing year, month, day, and so on. If you need to divide by more complex units of time, such as by week or by quarter, use theTRUNC()
function instead.Return type:
BIGINT
Examples:
EXTRACT(DAY FROM DATE'2019-08-17')
returns17
.If you specify
'MILLISECOND'
for the unit argument, the function returns the seconds component and the milliseconds component.EXTRACT(CAST('2006-05-12 18:27:28.123456789' AS TIMESTAMP), 'MILLISECOND')
returns28123
. - FROM_TIMESTAMP(TIMESTAMP datetime, STRING pattern), FROM_TIMESTAMP(STRING datetime, STRING pattern)
-
Purpose: Converts a
TIMESTAMP
value into a string representing the same value.Return type:
STRING
Usage notes:
The
FROM_TIMESTAMP()
function provides a flexible way to convertTIMESTAMP
values into arbitrary string formats for reporting purposes.Because Impala implicitly converts string values into
TIMESTAMP
, you can pass date/time values represented as strings (in the standardyyyy-MM-dd HH:mm:ss.SSS
format) to this function. The result is a string using different separator characters, order of fields, spelled-out month names, or other variation of the date/time string representation.The allowed tokens for the pattern string are the same as for the
FROM_UNIXTIME()
function. - FROM_UNIXTIME(BIGINT unixtime[, STRING pattern])
-
Purpose: Converts the number of seconds from the Unix epoch
to the specified time into a string in the local time zone.
Return type:
STRING
The pattern string supports the following subset of Java SimpleDateFormat.
Pattern Description y
Year M
Month d
Day H
Hour m
Minute s
Second S
Fractional second +/-hh:mm
Time zone offset +/-hhmm
Time zone offset +/-hh
Time zone offset The following rules apply to the pattern string:
- The pattern string is case-sensitive.
- All fields are variable length, and thus must use separators to specify the boundaries of the fields, with the exception of the time zone values.
- Time zone offset formats must be at the end of the pattern string.
- Formatting character groups can appear in any order along
with any separators except for the time zone offset. For
example:
-
yyyy/MM/dd
-
dd-MMM-yy
-
(dd)(MM)(yyyy) HH:mm:ss
-
yyyy-MM-dd HH:mm:ss+hh:mm
-
In Impala 1.3 and later, you can switch the order of elements, use alternative separator characters, and use a different number of placeholders for each unit. Adding more instances of
y
,d
,H
, and so on produces output strings zero-padded to the requested number of characters. The exception isM
for months, whereM
produces a non-padded value such as3
,MM
produces a zero-padded value such as03
,MMM
produces an abbreviated month name such asMar
, and sequences of 4 or moreM
are not allowed.A date string including all fields could be
'yyyy-MM-dd HH:mm:ss.SSSSSS'
,'dd/MM/yyyy HH:mm:ss.SSSSSS'
,'MMM dd, yyyy HH.mm.ss (SSSSSS)'
or other combinations of placeholders and separator characters.Usage notes:
The way this function deals with time zones when converting to or from
TIMESTAMP
values is affected by the‑‑use_local_tz_for_unix_timestamp_conversions
startup flag for the impalad daemon. - FROM_UTC_TIMESTAMP(TIMESTAMP ts, STRING timezone)
-
Purpose: Converts a specified UTC timestamp value into the appropriate value for
a specified time zone.
Return type:
TIMESTAMP
Usage notes: Often used to translate UTC time zone data stored in a table back to the local date and time for reporting. The opposite of the
TO_UTC_TIMESTAMP()
function.To determine the time zone of the server you are connected to, you can call the
TIMEOFDAY()
function, which includes the time zone specifier in its return value. Remember that with cloud computing, the server you interact with might be in a different time zone than you are, or different sessions might connect to servers in different time zones, or a cluster might include servers in more than one time zone.See discussion of time zones in TIMESTAMP data type for information about using this function for conversions between the local time zone and UTC.
- HOUR(TIMESTAMP ts)
-
Purpose: Returns the hour field from a
TIMESTAMP
field.Return type:
INT
- HOURS_ADD(TIMESTAMP date, INT hours), HOURS_ADD(TIMESTAMP date, BIGINT hours)
-
Purpose: Returns the specified date and time plus some number
of hours.
Return type:
TIMESTAMP
- HOURS_SUB(TIMESTAMP date, INT hours), HOURS_SUB(TIMESTAMP date, BIGINT hours)
-
Purpose: Returns the specified date and time minus some
number of hours.
Return type:
TIMESTAMP
- INT_MONTHS_BETWEEN(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate)
-
Purpose: Returns the number of months from
startdate to enddate,
representing only the full months that passed.
Return type:
INT
Usage notes:
Typically used in business contexts, for example to determine whether a specified number of months have passed or whether some end-of-month deadline was reached.
The method of determining the number of elapsed months includes some special handling of months with different numbers of days that creates edge cases for dates between the 28th and 31st days of certain months. See
MONTHS_BETWEEN()
for details. TheINT_MONTHS_BETWEEN()
result is essentially theFLOOR()
of theMONTHS_BETWEEN()
result.If either value is
NULL
, which could happen for example when converting a nonexistent date string such as'2015-02-29'
to aTIMESTAMP
, the result is alsoNULL
.If the first argument represents an earlier time than the second argument, the result is negative.
- LAST_DAY(TIMESTAMP / DATE ts)
-
Purpose: Returns the beginning of the last calendar day in
the same month of ts. Return type:
- Returns
TIMESTAMP
if ts is of theTIMESTAMP
type. - Returns
DATE
if ts is of theDATE
type.
Usage notes:
If the input argument does not represent a valid Impala
TIMESTAMP
including both date and time portions, the function returnsNULL
. For example, if the input argument is a string that cannot be implicitly cast toTIMESTAMP
, does not include a date portion, or is out of the allowed range for ImpalaTIMESTAMP
values, the function returnsNULL
. - Returns
- MICROSECONDS_ADD(TIMESTAMP date, INT microseconds), MICROSECONDS_ADD(TIMESTAMP date, BIGINT microseconds)
-
Purpose: Returns the specified date and time plus some number
of microseconds.
Return type:
TIMESTAMP
- MICROSECONDS_SUB(TIMESTAMP date, INT microseconds), MICROSECONDS_SUB(TIMESTAMP date, BIGINT microseconds)
-
Purpose: Returns the specified date and time minus some
number of microseconds.
Return type:
TIMESTAMP
- MILLISECOND(TIMESTAMP ts)
-
Purpose: Returns the millisecond portion of a
TIMESTAMP
value.Return type:
INT
Usage notes:
The millisecond value is truncated, not rounded, if the
TIMESTAMP
value contains more than 3 significant digits to the right of the decimal point. - MILLISECONDS_ADD(TIMESTAMP date, INT milliseconds), MILLISECONDS_ADD(TIMESTAMP date, BIGINT milliseconds)
-
Purpose: Returns the specified date and time plus some number
of milliseconds.
Return type:
TIMESTAMP
- MILLISECONDS_SUB(TIMESTAMP date, INT milliseconds), MILLISECONDS_SUB(TIMESTAMP date, BIGINT milliseconds)
-
Purpose: Returns the specified date and time minus some
number of milliseconds.
Return type:
TIMESTAMP
- MINUTE(TIMESTAMP date)
-
Purpose: Returns the minute field from a
TIMESTAMP
value.Return type:
INT
- MINUTES_ADD(TIMESTAMP date, INT minutes), MINUTES_ADD(TIMESTAMP date, BIGINT minutes)
-
Purpose: Returns the specified date and time plus some number
of minutes.
Return type:
TIMESTAMP
- MINUTES_SUB(TIMESTAMP date, INT minutes), MINUTES_SUB(TIMESTAMP date, BIGINT minutes)
-
Purpose: Returns the specified date and time minus some
number of minutes.
Return type:
TIMESTAMP
- MONTH(TIMESTAMP / DATE date)
-
Purpose: Returns the month field, represented as an integer,
from the date argument.
Return type:
INT
- MONTHNAME(TIMESTAMP / DATE date)
-
Purpose: Returns the month name of the
date argument.
Return type:
STRING
- MONTHS_ADD(TIMESTAMP / DATE date, INT / BIGINT months)
-
Purpose: Returns the value with the number of
months added to date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
Usage notes:
If date is the last day of a month, the return date will fall on the last day of the target month, e.g.
MONTHS_ADD(DATE'2019-01-31', 1)
returnsDATE'2019-02-28'
. - If date is
- MONTHS_BETWEEN(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate)
- Purpose: Returns the number of months from startdate to enddate.
- MONTHS_SUB(TIMESTAMP / DATE date, INT / BIGINT months)
-
Purpose: Returns the value with the number of
months subtracted from date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
Usage notes:
If date is the last day of a month, the return date will fall on the last day of the target month, e.g.
MONTHS_SUB(DATE'2019-02-28', 1)
returnsDATE'2019-01-31'
. - If date is
- NANOSECONDS_ADD(TIMESTAMP date, INT nanoseconds), NANOSECONDS_ADD(TIMESTAMP date, BIGINT nanoseconds)
-
Purpose: Returns the specified date and time plus some number
of nanoseconds.
Return type:
TIMESTAMP
Kudu considerations:
The nanosecond portion of an Impala
TIMESTAMP
value is rounded to the nearest microsecond when that value is stored in a Kudu table. - NANOSECONDS_SUB(TIMESTAMP date, INT nanoseconds), NANOSECONDS_SUB(TIMESTAMP date, BIGINT nanoseconds)
-
Purpose: Returns the specified date and time minus some
number of nanoseconds.
Return type:
TIMESTAMP
Kudu considerations:
The nanosecond portion of an Impala
TIMESTAMP
value is rounded to the nearest microsecond when that value is stored in a Kudu table. - NEXT_DAY(TIMESTAMP / DATE date, STRING weekday)
-
Purpose: Returns the date of the weekday
that follows the specified date.
Argument: The weekday is not case-sensitive.
The following values are accepted for weekday:
"Sunday"
/"Sun"
,"Monday"
/"Mon"
,"Tuesday"
/"Tue"
,"Wednesday"
/"Wed"
,"Thursday"
/"Thu"
,"Friday"
/"Fri"
,"Saturday"
/"Sat"
Return type:- Returns
TIMESTAMP
if date is of theTIMESTAMP
type. - Returns
DATE
if date is of theDATE
type.
Examples:
NEXT_DAY('2013-12-25','Saturday')
returns'2013-12-28 00:00:00'
which is the first Saturday after December 25, 2013. - Returns
- NOW()
-
Purpose: Returns the current date and time (in the local time
zone) as a
TIMESTAMP
value.Return type:
TIMESTAMP
Usage notes:
To find a date/time value in the future or the past relative to the current date and time, add or subtract an
INTERVAL
expression to the return value ofNOW()
.To produce a
TIMESTAMP
representing the current date and time that can be shared or stored without interoperability problems due to time zone differences, use theTO_UTC_TIMESTAMP()
function and specify the time zone of the server. WhenTIMESTAMP
data is stored in UTC form, any application that queries those values can convert them to the appropriate local time zone by calling the inverse function,FROM_UTC_TIMESTAMP()
.To determine the time zone of the server you are connected to, you can call the
timeofday()
function, which includes the time zone specifier in its return value. Remember that with cloud computing, the server you interact with might be in a different time zone than you are, or different sessions might connect to servers in different time zones, or a cluster might include servers in more than one time zone.Any references to the
NOW()
function are evaluated at the start of a query. All calls toNOW()
within the same query return the same value, and the value does not depend on how long the query takes. - QUARTER(TIMESTAMP / DATE date)
-
Purpose: Returns the quarter in the input
date argument as an integer value, 1, 2, 3, or
4, where 1 represents January 1 through March 31.
Return type:
INT
- SECOND(TIMESTAMP date)
-
Purpose: Returns the second field from a
TIMESTAMP
value.Return type:
INT
- SECONDS_ADD(TIMESTAMP date, INT seconds), SECONDS_ADD(TIMESTAMP date, BIGINT seconds)
-
Purpose: Returns the specified date and time plus some number
of seconds.
Return type:
TIMESTAMP
- SECONDS_SUB(TIMESTAMP date, INT seconds), SECONDS_SUB(TIMESTAMP date, BIGINT seconds)
-
Purpose: Returns the specified date and time minus some
number of seconds.
Return type:
TIMESTAMP
- SUBDATE(TIMESTAMP / DATE date, INT / BIGINT days)
-
Purpose: Subtracts days from
date and returns the new date value.
The days value can be negative, which gives the same result as the
ADDDATE()
function.Return type:- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
- If date is
- TIMEOFDAY()
-
Purpose: Returns a string representation of the current date
and time, according to the time of the local system, including any
time zone designation.
Return type:
STRING
Usage notes:
The result value represents similar information as the
now()
function, only as aSTRING
type and with somewhat different formatting. For example, the day of the week and the time zone identifier are included. This function is intended primarily for compatibility with SQL code from other systems that also have atimeofday()
function. Prefer to usenow()
if practical for any new Impala code. - TIMESTAMP_CMP(TIMESTAMP t1, TIMESTAMP t2)
-
Purpose: Tests if one
TIMESTAMP
value is newer than, older than, or identical to anotherTIMESTAMP
-
If the first argument represents a later point in time than the second argument, the result is 1.
-
If the first argument represents an earlier point in time than the second argument, the result is -1.
-
If the first and second arguments represent identical points in time, the result is 0.
-
If either argument is
NULL
, the result isNULL
.
Return type:
INT
(either -1, 0, 1, orNULL
)Usage notes:
A comparison function for
TIMESTAMP
values that only tests whether the date and time increases, decreases, or stays the same. Similar to theSIGN()
function for numeric values. -
- TO_DATE(TIMESTAMP ts)
-
Purpose: Returns a string representation of the date field
from the ts argument.
Return type:
STRING
- TO_TIMESTAMP(BIGINT unixtime), TO_TIMESTAMP(STRING date, STRING pattern)
-
Purpose: Converts an integer or string representing a
date/time value into the corresponding
TIMESTAMP
value.Return type:
TIMESTAMP
Usage notes:
An integer argument represents the number of seconds past the epoch (midnight on January 1, 1970). It is the converse of the
UNIX_TIMESTAMP()
function, which produces aBIGINT
representing the number of seconds past the epoch.A string argument, plus another string argument representing the pattern, turns an arbitrary string representation of a date and time into a true
TIMESTAMP
value. The ability to parse many kinds of date and time formats allows you to deal with temporal data from diverse sources, and if desired to convert to efficientTIMESTAMP
values during your ETL process. UsingTIMESTAMP
directly in queries and expressions lets you perform date and time calculations without the overhead of extra function calls and conversions each time you reference the applicable columns.Examples:
The following examples demonstrate how to convert an arbitrary string representation to
TIMESTAMP
based on a pattern string:select to_timestamp('Sep 25, 1984', 'MMM dd, yyyy'); +----------------------------------------------+ | to_timestamp('sep 25, 1984', 'mmm dd, yyyy') | +----------------------------------------------+ | 1984-09-25 00:00:00 | +----------------------------------------------+ select to_timestamp('1984/09/25', 'yyyy/MM/dd'); +------------------------------------------+ | to_timestamp('1984/09/25', 'yyyy/mm/dd') | +------------------------------------------+ | 1984-09-25 00:00:00 | +------------------------------------------+
The following examples show how to convert a
BIGINT
representing seconds past epoch into aTIMESTAMP
value:-- One day past the epoch. select to_timestamp(24 * 60 * 60); +----------------------------+ | to_timestamp(24 * 60 * 60) | +----------------------------+ | 1970-01-02 00:00:00 | +----------------------------+ -- 60 seconds in the past. select now() as 'current date/time', unix_timestamp(now()) 'now in seconds', to_timestamp(unix_timestamp(now()) - 60) as '60 seconds ago'; +-------------------------------+----------------+---------------------+ | current date/time | now in seconds | 60 seconds ago | +-------------------------------+----------------+---------------------+ | 2017-10-01 22:03:46.885624000 | 1506895426 | 2017-10-01 22:02:46 | +-------------------------------+----------------+---------------------+
- TO_UTC_TIMESTAMP(TIMESTAMP ts, STRING timezone)
-
Purpose: Converts a specified timestamp value in a specified time zone into the
corresponding value for the UTC time zone.
Return type:
TIMESTAMP
Usage notes:
Often used in combination with the
now()
function, to translate local date and time values to the UTC time zone for consistent representation on disk. The opposite of theFROM_UTC_TIMESTAMP()
function.See discussion of time zones in TIMESTAMP data type for information about using this function for conversions between the local time zone and UTC.
Examples:
The simplest use of this function is to turn a local date/time value to one with the standardized UTC time zone. Because the time zone specifier is not saved as part of the Impala
TIMESTAMP
value, all applications that refer to such data must agree in advance which time zone the values represent. If different parts of the ETL cycle, or different instances of the application, occur in different time zones, the ideal reference point is to convert allTIMESTAMP
values to UTC for storage.select now() as 'Current time in California USA', to_utc_timestamp(now(), 'PDT') as 'Current time in Greenwich UK'; +--------------------------------+-------------------------------+ | current time in california usa | current time in greenwich uk | +--------------------------------+-------------------------------+ | 2016-06-01 15:52:08.980072000 | 2016-06-01 22:52:08.980072000 | +--------------------------------+-------------------------------+
Once a value is converted to the UTC time zone by
TO_UTC_TIMESTAMP()
, it can be converted back to the local time zone withFROM_UTC_TIMESTAMP()
. You can combine these functions using different time zone identifiers to convert aTIMESTAMP
between any two time zones. This example starts with aTIMESTAMP
value representing Pacific Daylight Time, converts it to UTC, and converts it to the equivalent value in Eastern Daylight Time.select now() as 'Current time in California USA', from_utc_timestamp ( to_utc_timestamp(now(), 'PDT'), 'EDT' ) as 'Current time in New York, USA'; +--------------------------------+-------------------------------+ | current time in california usa | current time in new york, usa | +--------------------------------+-------------------------------+ | 2016-06-01 18:14:12.743658000 | 2016-06-01 21:14:12.743658000 | +--------------------------------+-------------------------------+
- TRUNC(TIMESTAMP / DATE ts, STRING unit)
-
Purpose: Returns the ts truncated to the
unit specified.
Argument: The unit argument is not case-sensitive. This argument string can be one of:
Unit Supported for TIMESTAMP ts Supported for DATE ts Description 'SYYYY'
'YYYY'
'YEAR'
'SYEAR'
'YYY'
'YY'
'Y'
Yes Yes Year 'Q'
Yes Yes Quarter 'MONTH'
'MON'
'MM'
'RM'
Yes Yes Month 'WW'
Yes Yes The most recent date that is the same day of the week as the first day of the year 'W'
Yes Yes The most recent date that is the same day of the week as the first day of the month 'DDD'
'DD'
'J'
Yes Yes Day 'DAY'
'DY'
'D'
Yes Yes Starting day of the week (Monday) 'HH'
'HH12'
'HH24'
Yes No Hour. A TIMESTAMP
value truncated to the hour is always represented in 24-hour notation, even for theHH12
argument string.'MI'
Yes No Minute Usage notes:
The
TIMESTAMP
form is typically used inGROUP BY
queries to aggregate results from the same hour, day, week, month, quarter, and so on. You can also use this function in anINSERT ... SELECT
into a partitioned table to divideTIMESTAMP
values into the correct partition.Return type:-
TIMESTAMP
if the first argument, ts, isTIMESTAMP
. -
DATE
if the first argument, ts, isDATE
.
Example:
TRUNC(DATE'2019-05-08','YEAR')
returns2019-01-01
.TRUNC(DATE'2019-05-08', 'QUARTER')
returns2019-04-01
. -
- UNIX_TIMESTAMP(), UNIX_TIMESTAMP(STRING datetime), UNIX_TIMESTAMP(STRING datetime, STRING pattern), UNIX_TIMESTAMP(TIMESTAMP datetime)
-
Purpose: Returns a Unix time, which is a number of seconds
elapsed since '1970-01-01 00:00:00' UTC. If called with no argument,
the current date and time is converted to its Unix time. If called
with arguments, the first argument represented as the
TIMESTAMP
orSTRING
is converted to its Unix time.Return type:
BIGINT
Usage notes:
See
FROM_UNIXTIME()
for details about the patterns you can use in the pattern string to represent the position of year, month, day, and so on in thedate
string. In Impala 1.3 and higher, you have more flexibility to switch the positions of elements and use different separator characters.You can include a trailing uppercase
Z
qualifier to indicateZulu
time, a synonym for UTC.You can include a timezone offset specified as minutes and hours, provided you also specify the details in the pattern string argument. The offset is specified in the pattern string as a plus or minus sign followed by
hh:mm
,hhmm
, orhh
. Thehh
must be lowercase, to distinguish it from theHH
represent hours in the actual time value. Currently, only numeric timezone offsets are allowed, not symbolic names.Built-in functions that accept or return integers representing
TIMESTAMP
values use theBIGINT
type for parameters and return values, rather thanINT
. This change lets the date and time functions avoid an overflow error that would otherwise occur on January 19th, 2038 (known as theYear 2038 problem
orY2K38 problem
). This change affects theFROM_UNIXTIME()
andUNIX_TIMESTAMP()
functions. You might need to change application code that interacts with these functions, change the types of columns that store the return values, or addCAST()
calls to SQL statements that call these functions.UNIX_TIMESTAMP()
andFROM_UNIXTIME()
are often used in combination to convert aTIMESTAMP
value into a particular string format. For example,FROM_UNIXTIME(UNIX_TIMESTAMP(NOW() + interval 3 days), 'yyyy/MM/dd HH:mm')
.The way this function deals with time zones when converting to or from
TIMESTAMP
values is affected by the‑‑use_local_tz_for_unix_timestamp_conversions
startup flag for the impalad daemon. See TIMESTAMP data type for details about how Impala handles time zone considerations for theTIMESTAMP
data type.Examples:
The following examples show different ways of turning the same date and time into an integer value. A pattern string that Impala recognizes by default is interpreted as a UTC date and time. The trailing
Z
is a confirmation that the timezone is UTC. If the date and time string is formatted differently, a second argument specifies the position and units for each of the date and time values.The final two examples show how to specify a timezone offset of Pacific Daylight Saving Time, which is 7 hours earlier than UTC. You can use the numeric offset
-07:00
and the equivalent suffix of-hh:mm
in the pattern string, or specify the mnemonic name for the time zone in a call toTO_UTC_TIMESTAMP()
. This particular date and time expressed in PDT translates to a different number than the same date and time expressed in UTC.-- 3 ways of expressing the same date/time in UTC and converting to an integer. select unix_timestamp('2015-05-15 12:00:00'); +---------------------------------------+ | unix_timestamp('2015-05-15 12:00:00') | +---------------------------------------+ | 1431691200 | +---------------------------------------+ select unix_timestamp('2015-05-15 12:00:00Z'); +----------------------------------------+ | unix_timestamp('2015-05-15 12:00:00z') | +----------------------------------------+ | 1431691200 | +----------------------------------------+ select unix_timestamp ( 'May 15, 2015 12:00:00', 'MMM dd, yyyy HH:mm:ss' ) as may_15_month_day_year; +-----------------------+ | may_15_month_day_year | +-----------------------+ | 1431691200 | +-----------------------+ -- 2 ways of expressing the same date and time but in a different timezone. -- The resulting integer is different from the previous examples. select unix_timestamp ( '2015-05-15 12:00:00-07:00', 'yyyy-MM-dd HH:mm:ss-hh:mm' ) as may_15_year_month_day; +-----------------------+ | may_15_year_month_day | +-----------------------+ | 1431716400 | +-----------------------+ select unix_timestamp (to_utc_timestamp( '2015-05-15 12:00:00', 'PDT') ) as may_15_pdt; +------------+ | may_15_pdt | +------------+ | 1431716400 | +------------+
- UTC_TIMESTAMP()
-
Purpose: Returns a
TIMESTAMP
corresponding to the current date and time in the UTC time zone.Return type:
TIMESTAMP
Examples:
Similar to the
NOW()
orCURRENT_TIMESTAMP()
functions, but does not use the local time zone as those functions do. UseUTC_TIMESTAMP()
to recordTIMESTAMP
values that are interoperable with servers around the world, in arbitrary time zones, without the need for additional conversion functions to standardize the time zone of each value representing a date/time.For working with date/time values represented as integer values, you can convert back and forth between
TIMESTAMP
andBIGINT
with theUNIX_MICROS_TO_UTC_TIMESTAMP()
andUTC_TO_UNIX_MICROS()
functions. The integer values represent the number of microseconds since the Unix epoch (midnight on January 1, 1970).Examples:
The following example shows how
NOW()
andCURRENT_TIMESTAMP()
represent the current date/time in the local time zone (in this case, UTC-7), whileutc_timestamp()
represents the same date/time in the standardized UTC time zone:select now(), utc_timestamp(); +-------------------------------+-------------------------------+ | now() | utc_timestamp() | +-------------------------------+-------------------------------+ | 2017-10-01 23:33:58.919688000 | 2017-10-02 06:33:58.919688000 | +-------------------------------+-------------------------------+ select current_timestamp(), utc_timestamp(); +-------------------------------+-------------------------------+ | current_timestamp() | utc_timestamp() | +-------------------------------+-------------------------------+ | 2017-10-01 23:34:07.400642000 | 2017-10-02 06:34:07.400642000 | +-------------------------------+-------------------------------+
- WEEK(TIMESTAMP / DATE date), WEEKOFYEAR(TIMESTAMP / DATE date)
-
Purpose: Returns the corresponding week (1-53) from the
date argument.
Return type:
INT
- WEEKS_ADD(TIMESTAMP / DATE date, INT / BIGINT weeks)
-
Purpose: Returns the value with the number of
weeks added to date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
- If date is
- WEEKS_SUB(TIMESTAMP / DATE date, INT / BIGINT weeks)
-
Purpose: Returns the value with the number of
weeks subtracted from date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
- If date is
- YEAR(TIMESTAMP / DATE date)
-
Purpose: Returns the year field from the
date argument.
Return type:
INT
- YEARS_ADD(TIMESTAMP / DATE date, INT / BIGINT years)
-
Purpose: Returns the value with the number of
years added to date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
Usage notes:
If the equivalent date does not exist in the year of the result due to a leap year, the date is changed to the last day of the appropriate month.
- If date is
- YEARS_SUB(TIMESTAMP / DATE date, INT / BIGINT years)
-
Purpose: Returns the value with the number of
years subtracted from date. Return type:
- If date is
TIMESTAMP
, returnsTIMESTAMP
. - If date is
DATE
, returnsDATE
.
Usage notes:
If the equivalent date does not exist in the year of the result due to a leap year, the date is changed to the last day of the appropriate month.
- If date is