Hive date function

=================================================================

[cloudera@quickstart ~]$ cat test.csv

1,13-10-2010,Low

2,01-10-2012,High

3,01-10-2012,High

4,10-07-2011,Low

==================================================================

[cloudera@quickstart ~]$ hive

hive> create database datedb;

OK

Time taken: 0.566 seconds

==================================================================

hive> use datedb;

OK

Time taken: 0.104 second

==================================================================

hive> create table dateDemo(ID INT, demo_date String,priority STRING) row format delimited fields terminated by ',';

OK

Time taken: 1.413 seconds

==================================================================

hive>load data local inpath 'test.csv' overwrite into table dateDemo;

Loading data to table datedb.datedemo

Table datedb.datedemo stats: [numFiles=1, numRows=0, totalSize=71, rawDataSize=0]

OK

Time taken: 1.383 seconds

==================================================================

hive> select * from dateDemo;

OK

1 13-10-2010 Low

2 01-10-2012 High

3 01-10-2012 High

4 10-07-2011 Low

==================================================================

hive>

>

> select * from dateDemo where demo_date='01-10-2012';

OK

2 01-10-2012 High

3 01-10-2012 High

==================================================================

hive> select ID, cast(to_date(from_unixtime(unix_timestamp(demo_date, 'dd-MM-yyyy'))) as date) from dateDemo;

OK

1 2010-10-13

2 2012-10-01

3 2012-10-01

4 2011-07-10

Time taken: 0.152 seconds, Fetched: 4 row(s)

==================================================================

hive> select ID, from_unixtime(unix_timestamp(demo_date, 'dd-MM-yyyy'),'yyyyMMdd') from dateDemo;

OK

1 20101013

2 20121001

3 20121001

4 20110710

Time taken: 0.093 seconds, Fetched: 4 row(s)

==================================================================