impala-shell ??

set request_pool = production;

unix_timsstamp long格式徐紧；from_unixtime timestamp格式

select from_unixtime(unix_timestamp(now() + interval 3 days), 'yyyy/MM/dd HH:mm');

http://www.cloudera.com/documentation/cdh/5-1-x/Impala/Installing-and-Using-Impala/ciiu_timestamp.html#timestamp

http://www.cloudera.com/documentation/cdh/5-1-x/Impala/Installing-and-Using-Impala/ciiu_datetime_functions.html#datetime_functions

Partitioning:

Although you cannot use aTIMESTAMPcolumn 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 originalTIMESTAMPvalues if desired, without duplicating data or wasting storage space. SeePartition Key Columnsfor more details on partitioning with date and time values.

date_add(timestamp startdate, int days),date_add(timestamp startdate,interval_expression)

Purpose:Adds a specified number of days to aTIMESTAMPvalue. The first argument can be a string, which is automatically cast toTIMESTAMPif it uses the recognized format, as described inTIMESTAMP Data Type. With anINTERVALexpression as the second argument, you can calculate a delta value using other units such as weeks, years, hours, seconds, and so on; seeTIMESTAMP Data Typefor details.

Return type:timestamp

date_sub(timestamp startdate, int days),date_sub(timestamp startdate,interval_expression)

Purpose:Subtracts a specified number of days from aTIMESTAMPvalue. The first argument can be a string, which is automatically cast toTIMESTAMPif it uses the recognized format, as described inTIMESTAMP Data Type. With anINTERVALexpression as the second argument, you can calculate a delta value using other units such as weeks, years, hours, seconds, and so on; seeTIMESTAMP Data Typefor details.

[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

[localhost:21000] > select from_unixtime(1392394861,"yyyy-MM-dd HH:mm:ss.SSSS");

+-------------------------------------------------------+

| from_unixtime(1392394861, 'yyyy-mm-dd hh:mm:ss.ssss') |

+-------------------------------------------------------+

| 2014-02-14 16:21:01.0000? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-------------------------------------------------------+

[localhost:21000] > select from_unixtime(1392394861,"yyyy-MM-dd");

+-----------------------------------------+

| from_unixtime(1392394861, 'yyyy-mm-dd') |

+-----------------------------------------+

| 2014-02-14? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------------------------------------+

[localhost:21000] > select from_unixtime(1392394861,"HH:mm:ss.SSSS");

+--------------------------------------------+

| from_unixtime(1392394861, 'hh:mm:ss.ssss') |

+--------------------------------------------+

| 16:21:01.0000? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+--------------------------------------------+

[localhost:21000] > select from_unixtime(1392394861,"HH:mm:ss");

+---------------------------------------+

| from_unixtime(1392394861, 'hh:mm:ss') |

+---------------------------------------+

| 16:21:01? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+---------------------------------------+

unix_timestamp()andfrom_unixtime()are often used in combination to convert aTIMESTAMPvalue into a particular string format. For example:

select from_unixtime(unix_timestamp(now() + interval 3 days), 'yyyy/MM/dd HH:mm');

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 UTC timezone.

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());