Date Manipulation - Extracting information using date_format

#1

Let us understand how to use date_format to extract information from date or timestamp.

Here is how we can get date related information such as year, month, day etc from date or timestamp.

DESCRIBE FUNCTION date_format;

SELECT current_timestamp;
SELECT current_timestamp, date_format(current_timestamp, 'YYYY');
SELECT current_timestamp, date_format(current_timestamp, 'YY');
SELECT current_timestamp, date_format(current_timestamp, 'MM');
SELECT current_timestamp, date_format(current_timestamp, 'dd');
SELECT current_timestamp, date_format(current_timestamp, 'DD');

Here is how we can get time related information such as hour, minute, seconds, milliseconds etc from timestamp.

SELECT current_timestamp, date_format(current_timestamp, 'HH');
SELECT current_timestamp, date_format(current_timestamp, 'hh');
SELECT current_timestamp, date_format(current_timestamp, 'mm');
SELECT current_timestamp, date_format(current_timestamp, 'ss');
SELECT current_timestamp, date_format(current_timestamp, 'SS'); // milliseconds

Here is how we can get the information from date or timestamp in the format we require.

SELECT date_format(current_timestamp, 'YYYYMM');
SELECT date_format(current_timestamp, 'YYYYMMdd');
SELECT date_format(current_timestamp, 'YYYY/MM/dd');

Practice hive on state of the art Big Data cluster - https://labs.itversity.com
You can sign up for our courses on Udemy using $10 coupons - Udemy Coupons - Big Data Courses


0 Likes