{"id":177,"date":"2019-10-19T18:00:08","date_gmt":"2019-10-19T18:00:08","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=177"},"modified":"2019-10-17T06:45:34","modified_gmt":"2019-10-17T06:45:34","slug":"dates-in-hive","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/10\/19\/dates-in-hive\/","title":{"rendered":"Dates In Hive"},"content":{"rendered":"\n

After previously spending a lot of time looking at dates in SQL Server and oracle, I wanted to do the same justice to Hive.<\/p>\n\n\n\n

Sample Date Functions<\/h2>\n\n\n\n

Hive is closer to oracle than to SQL Server when it comes to formatting datas, here are some samples:<\/p>\n\n\n\n

WITH sample_date_data AS\n(\nSELECT TIMESTAMP(from_unixtime(unix_timestamp())) AS the_timestamp\n)\nSELECT the_timestamp                      AS the_timestamp,\n       CAST(the_timestamp AS DATE)        AS the_date,\n     --TRUNC\n       TRUNC(the_timestamp, 'MM')         AS date_month,\n       TRUNC(the_timestamp, 'YY')         AS date_year,\n     --Common date parts\n       MONTH(the_timestamp)               AS month,\n       YEAR(the_timestamp)                AS year,\n     --DATE_FORMAT\n       DATE_FORMAT(the_timestamp, 'u')    AS daynumber_of_week,\n       DATE_FORMAT(the_timestamp, 'EEEE') AS day_name\n  FROM sample_date_data\n;<\/pre>\n\n\n\n
With the above example, one can also see the differentiation between a timestamp and a date (if you aren’t interested in the time, it’s easier not to store\/manipulate it).<\/p>\n\n\n\n
TRUNC<\/h3>\n\n\n\n
Like oracle, hive has the TRUNC<\/code> command which rounds down a date to the granularity specified. Unlike Oracle, this doesn’t include a default of day. For getting a whole day. I’d recommend casting to DATE<\/code>. <\/p>\n\n\n\n
The TRUNC<\/code> function is described as:<\/p>\n\n\n\n
Returns date truncated to the unit specified by the format (as of Hive 1.2.0). Supported formats: MONTH\/MON\/MM, YEAR\/YYYY\/YY. Example: trunc(‘2015-03-17’, ‘MM’) = 2015-03-01.<\/p>Language Manual UDF<\/a><\/cite><\/blockquote>\n\n\n\n
DATE_FORMAT<\/h3>\n\n\n\n
Similar to Oracle and SQL Server equivalents, this is based on a Java library for converting\/representing values stored as dates in readable\/different formats.<\/p>\n\n\n\n
The DATE_FORMAT<\/code> function is described as:<\/p>\n\n\n\n
Converts a date\/timestamp\/string to a value of string in the format specified by the date format fmt (as of Hive 1.2.0). Supported formats are Java SimpleDateFormat formats \u2013 https:\/\/docs.oracle.com\/javase\/7\/docs\/api\/java\/text\/SimpleDateFormat.html. The second argument fmt should be constant. Example: date_format(‘2015-04-08’, ‘y’) = ‘2015’.<\/p> Language Manual UDF<\/a> <\/cite><\/blockquote>\n\n\n\n
The format strings for this come straight from Java, Some common format strings are:<\/p>\n\n\n\n