| <?xml version="1.0" encoding="UTF-8"?> |
| <!DOCTYPE html |
| PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> |
| <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> |
| <head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> |
| |
| <meta name="copyright" content="(C) Copyright 2025" /> |
| <meta name="DC.rights.owner" content="(C) Copyright 2025" /> |
| <meta name="DC.Type" content="concept" /> |
| <meta name="DC.Title" content="TIMESTAMP Data Type" /> |
| <meta name="DC.Relation" scheme="URI" content="../topics/impala_datatypes.html" /> |
| <meta name="DC.Relation" scheme="URI" content="../topics/impala_custom_timezones.html" /> |
| <meta name="prodname" content="Impala" /> |
| <meta name="prodname" content="Impala" /> |
| <meta name="version" content="Impala 3.4.x" /> |
| <meta name="version" content="Impala 3.4.x" /> |
| <meta name="DC.Format" content="XHTML" /> |
| <meta name="DC.Identifier" content="timestamp" /> |
| <link rel="stylesheet" type="text/css" href="../commonltr.css" /> |
| <title>TIMESTAMP Data Type</title> |
| </head> |
| <body id="timestamp"> |
| |
| |
| <h1 class="title topictitle1" id="ariaid-title1">TIMESTAMP Data Type</h1> |
| |
| |
| |
| |
| |
| |
| <div class="body conbody"> |
| |
| <p class="p"> |
| In Impala, the <code class="ph codeph">TIMESTAMP</code> data type holds a value of date and time. It can |
| be decomposed into year, month, day, hour, minute and seconds fields, but with no time |
| zone information available, it does not correspond to any specific point in time. |
| </p> |
| |
| |
| <p class="p"> |
| Internally, the resolution of the time portion of a <code class="ph codeph">TIMESTAMP</code> value is in |
| nanoseconds. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Syntax:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| In the column definition of a <code class="ph codeph">CREATE TABLE</code> statement: |
| </p> |
| |
| |
| <pre class="pre codeblock"><code><var class="keyword varname">column_name</var> TIMESTAMP |
| |
| <var class="keyword varname">timestamp</var> [+ | -] INTERVAL <var class="keyword varname">interval</var> |
| DATE_ADD (<var class="keyword varname">timestamp</var>, INTERVAL <var class="keyword varname">interval</var> <var class="keyword varname">time_unit</var>)</code></pre> |
| |
| <p class="p"> |
| <strong class="ph b">Range:</strong> 1400-01-01 to 9999-12-31 |
| </p> |
| |
| |
| <p class="p"> |
| Out of range <code class="ph codeph">TIMESTAMP</code> values are converted to NULL. |
| </p> |
| |
| |
| <p class="p"> |
| The range of Impala <code class="ph codeph">TIMESTAMP</code> is different from the Hive |
| <code class="ph codeph">TIMESTAMP</code> type. Refer to |
| <a class="xref" href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-timestamp" target="_blank">Hive |
| documentation</a> for detail. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">INTERVAL expressions:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| You can perform date arithmetic by adding or subtracting a specified number of time units, |
| using the <code class="ph codeph">INTERVAL</code> keyword and the <code class="ph codeph">+</code> operator, the |
| <code class="ph codeph">-</code> operator, <code class="ph codeph">date_add()</code> or <code class="ph codeph">date_sub()</code>. |
| </p> |
| |
| |
| <div class="p"> |
| The following units are supported for <code class="ph codeph"><em class="ph i">time_unit</em></code> in the |
| <code class="ph codeph">INTERVAL</code> clause: |
| <ul class="ul"> |
| <li class="li"> |
| <code class="ph codeph">YEAR[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">MONTH[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">WEEK[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">DAY[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">HOUR[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">MINUTE[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">SECOND[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">MILLISECOND[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">MICROSECOND[S]</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">NANOSECOND[S]</code> |
| </li> |
| |
| </ul> |
| |
| </div> |
| |
| |
| <p class="p"> |
| You can only specify one time unit in each interval expression, for example |
| <code class="ph codeph">INTERVAL 3 DAYS</code> or <code class="ph codeph">INTERVAL 25 HOURS</code>, but you can |
| produce any granularity by adding together successive <code class="ph codeph">INTERVAL</code> values, |
| such as <code class="ph codeph"><var class="keyword varname">timestamp_value</var> + INTERVAL 3 WEEKS - INTERVAL 1 DAY + |
| INTERVAL 10 MICROSECONDS</code>. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Internal details:</strong> Represented in memory as a 16-byte value. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Time zones:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| By default, Impala stores and interprets <code class="ph codeph">TIMESTAMP</code> values in UTC time |
| zone when writing to data files, reading from data files, or converting to and from system |
| time values through functions. |
| </p> |
| |
| |
| <div class="p"> |
| When you set the |
| <code class="ph codeph">‑‑use_local_tz_for_unix_timestamp_conversions</code> startup flag to |
| <code class="ph codeph">TRUE</code>, Impala treats the <code class="ph codeph">TIMESTAMP</code> values specified in |
| the local time zone. The local time zone is determined in the following order with the |
| <code class="ph codeph">TIMEZONE</code> query option takes the highest precedence: |
| <ol class="ol"> |
| <li class="li"> |
| The <code class="ph codeph">TIMEZONE</code> query option |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">$TZ</code> environment variable |
| </li> |
| |
| |
| <li class="li"> |
| System time zone where the impalad coordinator runs |
| </li> |
| |
| </ol> |
| |
| </div> |
| |
| |
| <p class="p"> |
| The <code class="ph codeph">‑‑use_local_tz_for_unix_timestamp_conversions</code> setting can |
| be used to fix discrepancy in <code class="ph codeph">INTERVAL</code> operations. For example, a |
| <code class="ph codeph">TIMESTAMP + INTERVAL <var class="keyword varname">n-hours</var></code> can be affected by |
| Daylight Saving Time, which Impala does not consider by default as these operations are |
| applied as if the timestamp was in UTC. You can use the |
| <code class="ph codeph">--use_local_tz_for_unix_timestamp_conversions</code> setting to fix the issue. |
| </p> |
| |
| |
| <p class="p"> |
| See <a class="xref" href="impala_custom_timezones.html#custom_timezone">Customizing Time Zones</a> for configuring to use |
| custom time zone database and aliases. |
| </p> |
| |
| |
| <p class="p"> |
| See <a class="xref" href="impala_datetime_functions.html#datetime_functions">Impala Date and Time |
| Functions</a> for the list of functions affected by the |
| <code class="ph codeph">--use_local_tz_for_unix_timestamp_conversions</code> setting. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Time zone handling between Impala and Hive:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| Interoperability between Hive and Impala is different depending on the file format. |
| </p> |
| |
| |
| <ul class="ul"> |
| <li class="li"> |
| <em class="ph i">Text</em> |
| <p class="p"> |
| For text tables, <code class="ph codeph">TIMESTAMP</code> values can be written and read |
| interchangeably by Impala and Hive as Hive reads and writes <code class="ph codeph">TIMESTAMP</code> |
| values without converting with respect to time zones. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <em class="ph i">Parquet</em> |
| <div class="note note"><span class="notetitle">Note:</span> |
| This section only applies to <code class="ph codeph">INT96 TIMESTAMP</code>. See |
| <a class="xref" href="impala_parquet.html#parquet_data_types">Data Type Considerations for Parquet Tables</a> for information about Parquet |
| data types. |
| </div> |
| |
| <p class="p"> |
| When Hive writes to Parquet data files, the <code class="ph codeph">TIMESTAMP</code> values are |
| normalized to UTC from the local time zone of the host where the data was written. On |
| the other hand, Impala does not make any time zone adjustment when it writes or reads |
| <code class="ph codeph">INT96 TIMESTAMP</code> values to Parquet files. This difference in time zone |
| handling can cause potentially inconsistent results when Impala processes |
| <code class="ph codeph">TIMESTAMP</code> values in the Parquet files written by Hive. |
| </p> |
| |
| |
| <div class="p"> |
| To avoid incompatibility problems or having to code workarounds, you can specify one |
| or both of these impalad startup flags: |
| <ul class="ul"> |
| <li class="li"> |
| <code class="ph codeph">‑use_local_tz_for_unix_timestamp_conversions=true</code> |
| </li> |
| |
| |
| <li class="li"> |
| <code class="ph codeph">‑convert_legacy_hive_parquet_utc_timestamps=true</code> |
| </li> |
| |
| </ul> |
| |
| </div> |
| |
| |
| <p class="p"> |
| When the <code class="ph codeph">‑‑convert_legacy_hive_parquet_utc_timestamps</code> |
| setting is enabled, Impala recognizes the Parquet data files written by Hive, and |
| applies the same UTC-to-local-timezone conversion logic during the query as Hive does. |
| </p> |
| |
| |
| <p class="p"> |
| In <span class="keyword">Impala 3.0</span> and lower, the |
| <code class="ph codeph">‑‑convert_legacy_hive_parquet_utc_timestamps</code> setting had a severe |
| impact on multi-threaded performance. The new time zone implementation in |
| <span class="keyword">Impala 3.1</span> eliminated most of the performance overhead and made |
| Impala scale well to multiple threads. The |
| <code class="ph codeph">‑‑convert_legacy_hive_parquet_utc_timestamps</code> setting is turned |
| off by default for a performance reason. To avoid unexpected incompatibility problems, |
| you should turn on the option when processing <code class="ph codeph">TIMESTAMP</code> columns in |
| Parquet files written by Hive. |
| </p> |
| |
| |
| <p class="p"> |
| Hive currently cannot write <code class="ph codeph">INT64</code> <code class="ph codeph">TIMESTAMP</code> values. |
| </p> |
| |
| <p class="p"> |
| In <span class="keyword">Impala 3.2</span> and higher, <code class="ph codeph">INT64 |
| TIMESTAMP</code> values annotated with the <code class="ph codeph">TIMESTAMP_MILLIS</code> or |
| <code class="ph codeph">TIMESTAMP_MICROS</code> <code class="ph codeph">OriginalType</code> are assumed to be |
| always UTC normalized, so the UTC to local conversion will be always done. |
| <code class="ph codeph">INT64 TIMESTAMP</code> annotated with the <code class="ph codeph">TIMESTAMP</code> |
| <code class="ph codeph">LogicalType</code> specifies whether UTC to local conversion is necessary |
| depending on the Parquet metadata. |
| </p> |
| |
| </li> |
| |
| </ul> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Conversions:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| Impala automatically converts <code class="ph codeph">STRING</code> literals of the correct format |
| into <code class="ph codeph">TIMESTAMP</code> values. Timestamp values are accepted in the format |
| <code class="ph codeph">'yyyy‑MM‑dd HH:mm:ss.SSSSSS'</code>, and can consist of just the date, or |
| just the time, with or without the fractional second portion. For example, you can |
| specify <code class="ph codeph">TIMESTAMP</code> values such as <code class="ph codeph">'1966‑07‑30'</code>, |
| <code class="ph codeph">'08:30:00'</code>, or <code class="ph codeph">'1985‑09‑25 17:45:30.005'</code>. |
| </p> |
| <p class="p"> |
| Leading zeroes are not required in the numbers representing the date component, such as |
| month and date, or the time component, such as hour, minute, and second. For example, |
| Impala accepts both <code class="ph codeph">'2018‑1‑1 01:02:03'</code> and |
| <code class="ph codeph">'2018‑01‑01 1:2:3'</code> as valid. |
| </p> |
| <p class="p"> |
| In <code class="ph codeph">STRING</code> to <code class="ph codeph">TIMESTAMP</code> conversions, leading and |
| trailing white spaces, such as a space, a tab, a newline, or a carriage return, are |
| ignored. For example, Impala treats the following as equivalent: |
| '1999‑12‑01 01:02:03 ', ' 1999‑12‑01 01:02:03', |
| '1999‑12‑01 01:02:03\r\n\t'. |
| </p> |
| <div class="p"> |
| When you convert or cast a <code class="ph codeph">STRING</code> literal to |
| <code class="ph codeph">TIMESTAMP</code>, you can use the following separators between the date part |
| and the time part: |
| <ul class="ul"> |
| <li class="li"> |
| <p class="p"> |
| One or more space characters |
| </p> |
| |
| |
| <p class="p"> |
| Example: <code class="ph codeph">CAST('2001-01-09 01:05:01' AS TIMESTAMP)</code> |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| The character “T” |
| </p> |
| |
| |
| <p class="p"> |
| Example: <code class="ph codeph">CAST('2001-01-09T01:05:01' AS TIMESTAMP)</code> |
| </p> |
| |
| </li> |
| |
| </ul> |
| |
| </div> |
| |
| |
| <p class="p"> |
| <span class="ph"> Casting an integer or floating-point value |
| <code class="ph codeph">N</code> to <code class="ph codeph">TIMESTAMP</code> produces a value that is |
| <code class="ph codeph">N</code> seconds past the start of the epoch date (January 1, 1970). By |
| default, the result value represents a date and time in the UTC time zone. If the |
| setting <code class="ph codeph">‑‑use_local_tz_for_unix_timestamp_conversions=true</code> |
| is in effect, the resulting <code class="ph codeph">TIMESTAMP</code> represents a date and time in the |
| local time zone. </span> |
| </p> |
| |
| |
| <p class="p"> |
| In Impala 1.3 and higher, the <code class="ph codeph">FROM_UNIXTIME()</code> and |
| <code class="ph codeph">UNIX_TIMESTAMP()</code> functions allow a wider range of format strings, with |
| more flexibility in element order, repetition of letter placeholders, and separator |
| characters. In <span class="keyword">Impala 2.3</span> and higher, the |
| <code class="ph codeph">UNIX_TIMESTAMP()</code> function also allows a numeric timezone offset to be |
| specified as part of the input string. See |
| <a class="xref" href="impala_datetime_functions.html#datetime_functions">Impala Date and Time Functions</a> for details. |
| </p> |
| |
| |
| <p class="p"> |
| In Impala 2.2.0 and higher, built-in functions that accept or return integers |
| representing <code class="ph codeph">TIMESTAMP</code> values use the <code class="ph codeph">BIGINT</code> type for |
| parameters and return values, rather than <code class="ph codeph">INT</code>. This change lets the |
| date and time functions avoid an overflow error that would otherwise occur on January |
| 19th, 2038 (known as the |
| <a class="xref" href="http://en.wikipedia.org/wiki/Year_2038_problem" target="_blank"><span class="q">"Year |
| 2038 problem"</span> or <span class="q">"Y2K38 problem"</span></a>). This change affects the |
| <code class="ph codeph">FROM_UNIXTIME()</code> and <code class="ph codeph">UNIX_TIMESTAMP()</code> 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 <code class="ph codeph">CAST()</code> calls to |
| SQL statements that call these functions. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Partitioning:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| Although you cannot use a <code class="ph codeph">TIMESTAMP</code> column as a partition key, you can |
| extract the individual years, months, days, hours, and so on and partition based on those |
| columns. Because the partition key column values are represented in HDFS directory names, |
| rather than as fields in the data files themselves, you can also keep the original |
| <code class="ph codeph">TIMESTAMP</code> values if desired, without duplicating data or wasting storage |
| space. See <a class="xref" href="impala_partitioning.html#partition_key_columns">Partition Key Columns</a> for more |
| details on partitioning with date and time values. |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>[localhost:21000] > create table timeline (event string) partitioned by (happened timestamp); |
| ERROR: AnalysisException: Type 'TIMESTAMP' is not supported as partition-column type in column: happened |
| </code></pre> |
| |
| <p class="p"> |
| <strong class="ph b">NULL considerations:</strong> Casting any unrecognized <code class="ph codeph">STRING</code> value to |
| this type produces a <code class="ph codeph">NULL</code> value. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">HBase considerations:</strong> This data type is fully compatible with HBase tables. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Parquet consideration:</strong> <code class="ph codeph">INT96</code> encoded Parquet timestamps are |
| supported in Impala. <code class="ph codeph">INT64</code> timestamps are supported in |
| <span class="keyword">Impala 3.2</span> and higher. |
| </p> |
| |
| |
| <p class="p"></p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Parquet considerations:</strong> This type is fully compatible with Parquet tables. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Text table considerations:</strong> Values of this type are potentially larger in text |
| tables than in tables using Parquet or other binary formats. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Column statistics considerations:</strong> Because this type has a fixed size, the maximum |
| and average size fields are always filled in for column statistics, even before you run |
| the <code class="ph codeph">COMPUTE STATS</code> statement. |
| </p> |
| |
| |
| |
| |
| |
| |
| <p class="p"> |
| <strong class="ph b">Kudu considerations:</strong> |
| </p> |
| |
| |
| <div class="p"> |
| In <span class="keyword">Impala 2.9</span> and higher, you can include |
| <code class="ph codeph">TIMESTAMP</code> columns in Kudu tables, instead of representing the date and |
| time as a <code class="ph codeph">BIGINT</code> value. The behavior of <code class="ph codeph">TIMESTAMP</code> for |
| Kudu tables has some special considerations: |
| <ul class="ul"> |
| <li class="li"> |
| <p class="p"> |
| Any nanoseconds in the original 96-bit value produced by Impala are not stored, |
| because Kudu represents date/time columns using 64-bit values. The nanosecond |
| portion of the value is rounded, not truncated. Therefore, a |
| <code class="ph codeph">TIMESTAMP</code> value that you store in a Kudu table might not be |
| bit-for-bit identical to the value returned by a query. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| The conversion between the Impala 96-bit representation and the Kudu 64-bit |
| representation introduces some performance overhead when reading or writing |
| <code class="ph codeph">TIMESTAMP</code> columns. You can minimize the overhead during writes by |
| performing inserts through the Kudu API. Because the overhead during reads applies |
| to each query, you might continue to use a <code class="ph codeph">BIGINT</code> column to |
| represent date/time values in performance-critical applications. |
| </p> |
| |
| </li> |
| |
| |
| <li class="li"> |
| <p class="p"> |
| The Impala <code class="ph codeph">TIMESTAMP</code> type has a narrower range for years than the |
| underlying Kudu data type. Impala can represent years 1400-9999. If year values |
| outside this range are written to a Kudu table by a non-Impala client, Impala |
| returns <code class="ph codeph">NULL</code> by default when reading those |
| <code class="ph codeph">TIMESTAMP</code> values during a query. Or, if the |
| <code class="ph codeph">ABORT_ON_ERROR</code> query option is enabled, the query fails when it |
| encounters a value with an out-of-range year. |
| </p> |
| |
| </li> |
| |
| </ul> |
| |
| </div> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Restrictions:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| If you cast a <code class="ph codeph">STRING</code> with an unrecognized format to a |
| <code class="ph codeph">TIMESTAMP</code>, the result is <code class="ph codeph">NULL</code> rather than an error. Make |
| sure to test your data pipeline to be sure any textual date and time values are in a |
| format that Impala <code class="ph codeph">TIMESTAMP</code> can recognize. |
| </p> |
| |
| |
| <p class="p"> |
| Currently, Avro tables cannot contain <code class="ph codeph">TIMESTAMP</code> columns. If you need to |
| store date and time values in Avro tables, as a workaround you can use a |
| <code class="ph codeph">STRING</code> representation of the values, convert the values to |
| <code class="ph codeph">BIGINT</code> with the <code class="ph codeph">UNIX_TIMESTAMP()</code> function, or create |
| separate numeric columns for individual date and time fields using the |
| <code class="ph codeph">EXTRACT()</code> function. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Examples:</strong> |
| </p> |
| |
| |
| <p class="p"> |
| The following examples demonstrate using <code class="ph codeph">TIMESTAMP</code> values with built-in |
| functions: |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>select cast('1966-07-30' as timestamp); |
| select cast('1985-09-25 17:45:30.005' as timestamp); |
| select cast('08:30:00' as timestamp); |
| select hour('1970-01-01 15:30:00'); -- Succeeds, returns 15. |
| select hour('1970-01-01 15:30'); -- Returns NULL because seconds field required. |
| select hour('1970-01-01 27:30:00'); -- Returns NULL because hour value out of range. |
| select dayofweek('2004-06-13'); -- Returns 1, representing Sunday. |
| select dayname('2004-06-13'); -- Returns 'Sunday'. |
| select date_add('2004-06-13', 365); -- Returns 2005-06-13 with zeros for hh:mm:ss fields. |
| select day('2004-06-13'); -- Returns 13. |
| select datediff('1989-12-31','1984-09-01'); -- How many days between these 2 dates? |
| select now(); -- Returns current date and time in local timezone. |
| </code></pre> |
| |
| <p class="p"> |
| The following examples demonstrate using <code class="ph codeph">TIMESTAMP</code> values with |
| HDFS-backed tables: |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>create table dates_and_times (t timestamp); |
| insert into dates_and_times values |
| ('1966-07-30'), ('1985-09-25 17:45:30.005'), ('08:30:00'), (now()); |
| </code></pre> |
| |
| <p class="p"> |
| The following examples demonstrate using <code class="ph codeph">TIMESTAMP</code> values with Kudu |
| tables: |
| </p> |
| |
| |
| <pre class="pre codeblock"><code>create table timestamp_t (x int primary key, s string, t timestamp, b bigint) |
| partition by hash (x) partitions 16 |
| stored as kudu; |
| |
| -- The default value of now() has microsecond precision, so the final 3 digits |
| -- representing nanoseconds are all zero. |
| insert into timestamp_t values (1, cast(now() as string), now(), unix_timestamp(now())); |
| |
| -- Values with 1-499 nanoseconds are rounded down in the Kudu TIMESTAMP column. |
| insert into timestamp_t values (2, cast(now() + interval 100 nanoseconds as string), now() + interval 100 nanoseconds, unix_timestamp(now() + interval 100 nanoseconds)); |
| insert into timestamp_t values (3, cast(now() + interval 499 nanoseconds as string), now() + interval 499 nanoseconds, unix_timestamp(now() + interval 499 nanoseconds)); |
| |
| -- Values with 500-999 nanoseconds are rounded up in the Kudu TIMESTAMP column. |
| insert into timestamp_t values (4, cast(now() + interval 500 nanoseconds as string), now() + interval 500 nanoseconds, unix_timestamp(now() + interval 500 nanoseconds)); |
| insert into timestamp_t values (5, cast(now() + interval 501 nanoseconds as string), now() + interval 501 nanoseconds, unix_timestamp(now() + interval 501 nanoseconds)); |
| |
| -- The string representation shows how underlying Impala TIMESTAMP can have nanosecond precision. |
| -- The TIMESTAMP column shows how timestamps in a Kudu table are rounded to microsecond precision. |
| -- The BIGINT column represents seconds past the epoch and so if not affected much by nanoseconds. |
| select s, t, b from timestamp_t order by t; |
| +-------------------------------+-------------------------------+------------+ |
| | s | t | b | |
| +-------------------------------+-------------------------------+------------+ |
| | 2017-05-31 15:30:05.107157000 | 2017-05-31 15:30:05.107157000 | 1496244605 | |
| | 2017-05-31 15:30:28.868151100 | 2017-05-31 15:30:28.868151000 | 1496244628 | |
| | 2017-05-31 15:34:33.674692499 | 2017-05-31 15:34:33.674692000 | 1496244873 | |
| | 2017-05-31 15:35:04.769166500 | 2017-05-31 15:35:04.769167000 | 1496244904 | |
| | 2017-05-31 15:35:33.033082501 | 2017-05-31 15:35:33.033083000 | 1496244933 | |
| +-------------------------------+-------------------------------+------------+ |
| </code></pre> |
| |
| <p class="p"> |
| <strong class="ph b">Added in:</strong> Available in all versions of Impala. |
| </p> |
| |
| |
| <p class="p"> |
| <strong class="ph b">Related information:</strong> |
| </p> |
| |
| |
| <ul class="ul"> |
| <li class="li"> |
| <a class="xref" href="impala_literals.html#timestamp_literals">Timestamp Literals</a>. |
| </li> |
| |
| |
| <li class="li"> |
| To convert to or from different date formats, or perform date arithmetic, use the date |
| and time functions described in |
| <a class="xref" href="impala_datetime_functions.html#datetime_functions">Impala Date and Time Functions</a>. In |
| particular, the <code class="ph codeph">from_unixtime()</code> function requires a case-sensitive |
| format string such as <code class="ph codeph">"yyyy-MM-dd HH:mm:ss.SSSS"</code>, matching one of the |
| allowed variations of a <code class="ph codeph">TIMESTAMP</code> value (date plus time, only date, |
| only time, optional fractional seconds). |
| </li> |
| |
| |
| <li class="li"> |
| See <a class="xref" href="impala_langref_unsupported.html#langref_hiveql_delta">SQL Differences Between Impala and Hive</a> for |
| details about differences in <code class="ph codeph">TIMESTAMP</code> handling between Impala and |
| Hive. |
| </li> |
| |
| </ul> |
| |
| |
| </div> |
| |
| |
| <div class="related-links"> |
| <ul class="ullinks"> |
| <li class="link ulchildlink"><strong><a href="../topics/impala_custom_timezones.html">Customizing Time Zones</a></strong><br /> |
| </li> |
| </ul> |
| |
| <div class="familylinks"> |
| <div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_datatypes.html">Data Types</a></div> |
| </div> |
| </div></body> |
| </html> |
