| DESCRIBE FUNCTION date_format; |
| DESC FUNCTION EXTENDED date_format; |
| |
| set hive.local.time.zone=Africa/Johannesburg; |
| |
| explain select date_format('2015-04-08', 'EEEE'); |
| |
| --string date |
| select |
| date_format('2015-04-08', 'E'), |
| date_format('2015-04-08', 'G'), |
| date_format('2015-04-08', 'y'), |
| date_format('2015-04-08', 'Y'), |
| date_format('2015-04-08', 'MMM'), |
| date_format('2015-04-08', 'w'), |
| date_format('2015-04-08', 'W'), |
| date_format('2015-04-08', 'D'), |
| date_format('2015-04-08', 'd'), |
| date_format(cast(null as string), 'dd'); |
| |
| set hive.local.time.zone=Europe/Berlin; |
| |
| --string timestamp |
| select |
| date_format('2015-04-08 10:30:45', 'HH'), |
| date_format('2015-04-08 10:30:45', 'mm'), |
| date_format('2015-04-08 10:30:45', 'ss'), |
| date_format('2015-04-08 21:30:45', 'hh a'), |
| date_format('2015-04-08 10:30', 'dd'), |
| date_format('2015-04-08 10:30:45.123', 'S'), |
| date_format('2015-04-08T10:30:45', 'dd'), |
| date_format('2015-04-08 10', 'dd'), |
| date_format(cast(null as string), 'dd'); |
| |
| set hive.local.time.zone=Australia/Sydney; |
| |
| --date |
| select |
| date_format(cast('2015-04-08' as date), 'EEEE'), |
| date_format(cast('2015-04-08' as date), 'G'), |
| date_format(cast('2015-04-08' as date), 'yyyy'), |
| date_format(cast('2015-04-08' as date), 'YY'), |
| date_format(cast('2015-04-08' as date), 'MMM'), |
| date_format(cast('2015-04-08' as date), 'w'), |
| date_format(cast('2015-04-08' as date), 'W'), |
| date_format(cast('2015-04-08' as date), 'D'), |
| date_format(cast('2015-04-08' as date), 'd'), |
| date_format(cast(null as date), 'dd'); |
| |
| set hive.local.time.zone=Asia/Bangkok; |
| |
| --timestamp |
| select |
| date_format(cast('2015-04-08 10:30:45' as timestamp), 'HH'), |
| date_format(cast('2015-04-08 10:30:45' as timestamp), 'mm'), |
| date_format(cast('2015-04-08 10:30:45' as timestamp), 'ss'), |
| date_format(cast('2015-04-08 10:30:45' as timestamp), 'hh a'), |
| date_format(cast('2015-04-08 10:30:45' as timestamp), 'dd'), |
| date_format(cast('2015-04-08 10:30:45.123' as timestamp), 'SSS'), |
| date_format(cast('2015-04-08 10:30:45.123456789' as timestamp), 'SSS'), |
| date_format(cast(null as timestamp), 'HH'); |
| |
| -- wrong fmt |
| select |
| date_format('2015-04-08', ''), |
| date_format('2015-04-08', 'B'); |
| |
| -- with time zone |
| set hive.local.time.zone=Asia/Bangkok; |
| select date_format("2015-04-08 10:30:45","yyyy-MM-dd HH:mm:ss.SSS z"); |
| |
| set hive.local.time.zone=Australia/Sydney; |
| select date_format("2015-04-08 10:30:45","yyyy-MM-dd HH:mm:ss.SSS z"); |
| |
| set hive.local.time.zone=Europe/Berlin; |
| select date_format("2015-04-08 10:30:45","yyyy-MM-dd HH:mm:ss.SSS z"); |
| |
| --julian date |
| set hive.local.time.zone=UTC; |
| select date_format("1001-01-05","dd---MM--yyyy"); |
| |
| --dates prior to 1900 |
| set hive.local.time.zone=Asia/Bangkok; |
| select date_format('1400-01-14 01:01:10.123', 'yyyy-MM-dd HH:mm:ss.SSS z'); |
| select date_format('1800-01-14 01:01:10.123', 'yyyy-MM-dd HH:mm:ss.SSS z'); |
| |
| set hive.local.time.zone=Europe/Berlin; |
| select date_format('1400-01-14 01:01:10.123', 'yyyy-MM-dd HH:mm:ss.SSS z'); |
| select date_format('1800-01-14 01:01:10.123', 'yyyy-MM-dd HH:mm:ss.SSS z'); |
| |
| set hive.local.time.zone=Africa/Johannesburg; |
| select date_format('1400-01-14 01:01:10.123', 'yyyy-MM-dd HH:mm:ss.SSS z'); |
| select date_format('1800-01-14 01:01:10.123', 'yyyy-MM-dd HH:mm:ss.SSS z'); |