The underlying Impala data types for date and time data are TIMESTAMP and
DATE.
‑‑use_local_tz_for_unix_timestamp_conversions startup flag
for the impalad daemon:
FROM_UNIXTIME() and UNIX_TIMESTAMP() consider the
input values to always represent the UTC time zone.
CAST() a BIGINT
value to TIMESTAMP, or a TIMESTAMP value to
BIGINT. 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 the TIMESTAMP data type.
Function reference:
Impala supports the following date and time functions:
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
Usage notes:
Same as
MONTHS_ADD().
Available in Impala 1.4 and higher. For compatibility when porting code with vendor
extensions.
The days value can be negative, which gives the same result as
the SUBDATE() function.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
Any references to the CURRENT_DATE() function are evaluated at the
start of a query. All calls to CURRENT_DATE() within the same query
return the same value, and the value does not depend on how long the query takes.
Return type: DATE
NOW() function.
Return type: TIMESTAMP
0 if the dates are identical.
NULL if date1 or date2 is
NULL.
Return type: INT
EXTRACT(),
with the argument order reversed. Supports the same date and time units as
EXTRACT(). For compatibility with SQL code containing vendor
extensions.
Return type: BIGINT
TIMESTAMP
value. With an INTERVAL expression as the second argument, you can
calculate a delta value using other units such as weeks, years, hours, seconds, and so
on; see TIMESTAMP Data Type for details.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
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. The
INTERVAL 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 |
+---------------------+
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 |
Added in: Impala 2.11.0
Usage notes:
Although this function is similar to calling TRUNC() with a
TIMESTAMP or DATE argument, the order of arguments
and the recognized units are different between TRUNC() and
DATE_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 an INSERT ... SELECT into a partitioned table to
divide TIMESTAMP or DATE values into the correct
partition.
TIMESTAMP if the second argument, ts, is
TIMESTAMP.
DATE if the second argument, ts, is
DATE.
Examples:
DATE_TRUNC('HOUR', NOW()) returns 2017-12-05
13:00:00.
DATE_TRUNC('MILLENNIUM', DATE'2019-08-02') returns
2001-01-01.
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 the TIMESTAMP values differ by only 2
minutes.
Returns NULL for nonexistent dates, e.g. Feb 30,
or misformatted date strings, e.g. '1999-02-013'.
Return type: INT
'Sunday' to 'Saturday'.
Used in report-generating queries, as an alternative to calling
DAYOFWEEK() and turning that numeric return value into a string using
a CASE expression.
Return type: STRING
Return type: INT
Return type: INT
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
Argument: The unit argument value is not case-sensitive. The
unit 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 an INSERT ...
SELECT statement to insert into a partitioned table to split up
TIMESTAMP 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 the
TRUNC() function instead.
Return type: BIGINT
Examples:
EXTRACT(DAY FROM DATE'2019-08-17') returns 17.
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') returns 28123.
TIMESTAMP value into a string representing
the same value.
Return type: STRING
Usage notes:
The FROM_TIMESTAMP() function provides a flexible way to convert
TIMESTAMP 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 standard
yyyy-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.
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 |
The following rules apply to the pattern string:
yyyy/MM/dd
dd-MMM-yy
(dd)(MM)(yyyy) HH:mm:ss
yyyy-MM-dd HH:mm:ss
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 is M for months, where
M produces a non-padded value such as 3,
MM produces a zero-padded value such as 03,
MMM produces an abbreviated month name such as Mar,
and sequences of 4 or more M 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.
In Impala 2.2.0 and higher, built-in functions that accept or return integers
representing TIMESTAMP values use the BIGINT type for
parameters and return values, rather than INT. This change lets the
date and time functions avoid an overflow error that would otherwise occur on January
19th, 2038 (known as the
"Year
2038 problem" or "Y2K38 problem"). This change affects the
FROM_UNIXTIME() and UNIX_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 add CAST() calls to
SQL statements that call these functions.
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. See
TIMESTAMP Data Type for details about how
Impala handles time zone considerations for the TIMESTAMP data type.
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, in
Impala 2.3 and higher 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.
TIMESTAMP field.
Return type: INT
Return type: TIMESTAMP
Return type: TIMESTAMP
Return type: INT
Added in: Impala 2.3.0
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. The
INT_MONTHS_BETWEEN() result is essentially the
FLOOR() of the MONTHS_BETWEEN() result.
If either value is NULL, which could happen for example when
converting a nonexistent date string such as '2015-02-29' to a
TIMESTAMP, the result is also NULL.
If the first argument represents an earlier time than the second argument, the result is negative.
TIMESTAMP if ts is of the
TIMESTAMP type.
DATE if ts is of the
DATE type.
Added in: Impala 2.9.0
Usage notes:
If the input argument does not represent a valid Impala TIMESTAMP
including both date and time portions, the function returns NULL.
For example, if the input argument is a string that cannot be implicitly cast to
TIMESTAMP, does not include a date portion, or is out of the
allowed range for Impala TIMESTAMP values, the function returns
NULL.
Return type: TIMESTAMP
Return type: TIMESTAMP
TIMESTAMP value.
Return type: INT
Added in: Impala 2.5.0
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.
Return type: TIMESTAMP
Return type: TIMESTAMP
TIMESTAMP value.
Return type: INT
Return type: TIMESTAMP
Return type: TIMESTAMP
Return type: INT
Return type: STRING
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
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) returns DATE'2019-02-28'.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
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) returns DATE'2019-01-31'.
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.
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.
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"
TIMESTAMP if date is of the
TIMESTAMP type.
DATE if date is of the
DATE type.
Examples:
NEXT_DAY('2013-12-25','Saturday') returns '2013-12-28
00:00:00' which is the first Saturday after December 25, 2013.
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 of
NOW(). See
TIMESTAMP Data Type for examples.
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 the TO_UTC_TIMESTAMP() function and specify the
time zone of the server. When TIMESTAMP 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, in
Impala 2.3 and higher 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 to NOW() within the same query return the same
value, and the value does not depend on how long the query takes.
Return type: INT
TIMESTAMP value.
Return type: INT
Return type: TIMESTAMP
Return type: TIMESTAMP
The days value can be negative, which gives the same result as
the ADDDATE() function.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
Return type: STRING
Added in: Impala 2.3.0
Usage notes: The result value represents similar information as the
now() function, only as a STRING 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 a timeofday() function.
Prefer to use now() if practical for any new Impala code.
TIMESTAMP value is newer than, older
than, or identical to another TIMESTAMP
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 is
NULL.
Return type: INT (either -1, 0, 1, or NULL)
Added in: Impala 2.3.0
Usage notes:
Usage notes: A comparison function for TIMESTAMP values that
only tests whether the date and time increases, decreases, or stays the same.
Similar to the SIGN() function for numeric values.
Return type: STRING
TIMESTAMP value.
Return type: TIMESTAMP
Added in: Impala 2.3.0
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 a BIGINT 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 efficient TIMESTAMP values during your ETL process.
Using TIMESTAMP 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 a TIMESTAMP 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 |
+-------------------------------+----------------+---------------------+
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 the FROM_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 all TIMESTAMP 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
with FROM_UTC_TIMESTAMP(). You can combine these functions using
different time zone identifiers to convert a TIMESTAMP between any
two time zones. This example starts with a TIMESTAMP 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 |
+--------------------------------+-------------------------------+
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 |
|---|---|---|---|
|
|
Yes | Yes | Year |
|
|
Yes | Yes | Quarter |
|
|
Yes | Yes | Month |
|
|
Yes | Yes | The most recent date that is the same day of the week as the first day of the year |
|
|
Yes | Yes | The most recent date that is the same day of the week as the first day of the month |
|
|
Yes | Yes | Day |
|
|
Yes | Yes | Starting day of the week (Monday) |
|
|
Yes | No |
Hour. A TIMESTAMP value truncated to the hour is always
represented in 24-hour notation, even for the HH12
argument string.
|
'MI'
|
Yes | No | Minute |
Added in: The ability to truncate numeric values is new starting in Impala 2.10.
Usage notes:
The TIMESTAMP form 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 an INSERT ... SELECT into a
partitioned table to divide TIMESTAMP values into the correct
partition.
TIMESTAMP if the first argument, ts, is
TIMESTAMP.
DATE if the first argument, ts, is
DATE.
Example:
TRUNC(DATE'2019-05-08','YEAR') returns 2019-01-01.
TRUNC(DATE'2019-05-08', 'QUARTER') returns
2019-04-01.
TIMESTAMP or STRING 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 the date string. In Impala 1.3 and higher, you have
more flexibility to switch the positions of elements and use different separator
characters.
In Impala 2.2.3 and higher, you can include a trailing uppercase
Z qualifier to indicate "Zulu" time, a synonym for UTC.
In Impala 2.3 and higher, 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, or hh. The
hh must be lowercase, to distinguish it from the
HH represent hours in the actual time value. Currently, only
numeric timezone offsets are allowed, not symbolic names.
In Impala 2.2.0 and higher, built-in functions that accept or return integers
representing TIMESTAMP values use the BIGINT type for
parameters and return values, rather than INT. This change lets the
date and time functions avoid an overflow error that would otherwise occur on January
19th, 2038 (known as the
"Year
2038 problem" or "Y2K38 problem"). This change affects the
FROM_UNIXTIME() and UNIX_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 add CAST() calls to
SQL statements that call these functions.
UNIX_TIMESTAMP() and FROM_UNIXTIME() are often used in
combination to convert a TIMESTAMP value into a particular string
format. For example:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW() + interval 3 days),
'yyyy/MM/dd HH:mm') AS yyyy_mm_dd_hh_mm;
+------------------+
| yyyy_mm_dd_hh_mm |
+------------------+
| 2016/06/03 11:38 |
+------------------+
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 the TIMESTAMP 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 to
TO_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 |
+------------+
TIMESTAMP corresponding to the current date
and time in the UTC time zone.
Return type: TIMESTAMP
Added in: Impala 2.10
Usage notes:
Similar to the NOW() or CURRENT_TIMESTAMP()
functions, but does not use the local time zone as those functions do. Use
UTC_TIMESTAMP() to record TIMESTAMP 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 and BIGINT with
the UNIX_MICROS_TO_UTC_TIMESTAMP() and
UTC_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() and
CURRENT_TIMESTAMP() represent the current date/time in the local
time zone (in this case, UTC-7), while utc_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 |
+-------------------------------+-------------------------------+
Return type: INT
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
Return type: INT
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
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.
TIMESTAMP, returns
TIMESTAMP.
DATE, returns
DATE.
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.