Page History

cast(date as date)

Same date value

cast(timestamp as date)

The year/month/day of the timestamp is determined, based on the local timezone, and returned as a date value.

cast(string as date)

If the string is in the form 'YYYY-MM-DD', then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned.

cast(date as timestamp)

A timestamp value is generated corresponding to midnight of the year/month/day of the date value, based on the local timezone.

cast(date as string)

The year/month/day represented by the Date is formatted as a string in the form 'YYYY-MM-DD'.

Intervals of time units:

SECOND / MINUTE / DAY / MONTH / YEAR

INTERVAL '1' DAY

Hive 1.2.0 (HIVE-9792).

Year to month intervals, format: SY-M

S: optional sign (+/-)
Y: year count
M: month count

shorthand for:

INTERVAL '1' YEAR +
INTERVAL '2' MONTH

Day to second intervals, format: SD H:M:S.nnnnnn

S: optional sign (+/-)

shorthand for:

INTERVAL '1' DAY+
INTERVAL '2' HOUR +
INTERVAL '3' MINUTE +
INTERVAL '4' SECOND +
INTERVAL '5' NANO

Hive 1.2.0 (HIVE-9792).

aids query readability / portability 

Support for intervals with expressions:

this may involve other functions/

columns.
The expression must return with

a number (which is not

floating-point)

or with a string.

1 DAY
'1-2' YEAR TO MONTH
(1+dt) SECOND

INTERVAL 1 DAY
INTERVAL '1-2' YEARS TO MONTH
INTERVAL (1+dt) SECOND

Add timeunit aliases to aid portability / readability

:

SECONDS / MINUTES / HOURS / DAYS / WEEKS / MONTHS / YEARS