Hive Time Operation function

Hive time operation functionTags: hive function linux 2016-12-21 16:07 49 people read comments (0) Favorites Report Category: Hive

Date function Unix Timestamp date function: From_unixtime syntax: From_unixtime (bigint unixtime[, string format])

return value: String

Description: Converts a UNIX timestamp (the number of seconds from 1970-01-01 00:00:00 UTC to a specified time) to the time format of the current time zone

Example:

Hive> Select From_unixtime (1323308943, ' YyyyMMdd ') from dual;

20111208

Get the current UNIX timestamp function: Unix_timestamp syntax: Unix_timestamp ()

return value: bigint

Description: Gets the current time zone's Unix timestamp

Example:

Hive> Select Unix_timestamp () from dual;

1323309615

Date to UNIX timestamp function: Unix_timestamp syntax: Unix_timestamp (string date)

return value: bigint

Description: The conversion format is "Yyyy-mm-dd HH:mm:ss" date to the Unix timestamp. If the conversion fails, it returns 0.

Example:

Hive> Select Unix_timestamp (' 2011-12-07 13:01:03′) from dual;

1323234063

Specify Format Date Unix timestamp function: Unix_timestamp syntax: Unix_timestamp (string date, string pattern)

return value: bigint

Description: Converts the date in pattern format to the Unix time stamp. If the conversion fails, it returns 0.

Example:

Hive> Select Unix_timestamp (' 20111207 13:01:03′, ' YyyyMMdd HH:mm:ss ') from dual;

1323234063

Date-time Date function: To_date syntax: to_date (string timestamp)

return value: String

Description: Returns the date part in the DateTime field.

Example:

Hive> Select To_date (' 2011-12-08 10:03:01′) from dual;

2011-12-08

Date following year function: Year Syntax: Year (string date)

return value: int

Description: Returns the year of the date.

Example:

Hive> Select year (' 2011-12-08 10:03:01′) from dual;

2011

Hive> Select year (' 2012-12-08′) from dual;

2012

Date-to-month function: Month Syntax: month (string date)

return value: int

Description: Returns the month in the date.

Example:

Hive> Select month (' 2011-12-08 10:03:01′) from dual;

12

Hive> Select month (' 2011-08-08′) from dual;

8

Date: Day Syntax: Days (string date)

return value: int

Description: Returns the day in the date.

Example:

Hive> Select Day (' 2011-12-08 10:03:01′) from dual;

8

Hive> Select Day (' 2011-12-24′ ') from dual;

24

Date to hour function: Hour Syntax: Hour (string date)

return value: int

Description: Returns the hour in the date.

Example:

Hive> Select Hour (' 2011-12-08 10:03:01′) from dual;

10

Date to minute function: Minute Syntax: Minute (string date)

return value: int

Description: Returns the Minutes in the date.

Example:

hive> Select minute (' 2011-12-08 10:03:01′) from dual;

3

Date in seconds function: Second syntax: second (string date)

return value: int

Description: Returns the seconds in the date.

Example:

Hive> Select Second (' 2011-12-08 10:03:01′) from dual;

1

Date-cycle function: WeekOfYear syntax: WeekOfYear (string date)

return value: int

Description: Returns the date in the current number of weeks.

Example:

Hive> Select WeekOfYear (' 2011-12-08 10:03:01′) from dual;

49

Date comparison function: DateDiff syntax: DateDiff (String enddate, String startdate)

return value: int

Description: Returns the number of days after which the end date minus the start date.

Example:

Hive> Select DateDiff (' 2012-12-08′, ' 2012-05-09′ ') from dual;

213

Date increment function: Date_add syntax: Date_add (string startdate, int days)

return value: String

Description: Returns the start date StartDate the day after days.

Example:

Hive> Select Date_add (' 2012-12-08′,10) from dual;

2012-12-18

Date reduction function: Date_sub syntax: Date_sub (string startdate, int days)

return value: String

Description: Returns the start date startdate reduce days after day.

Example:

Hive> Select Date_sub (' 2012-12-08′,10) from dual;

2012-11-28