[HIVE-25268] date_format udf returns wrong results for dates prior to 1900 if the local timezone is other than UTC

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.0, 3.1.1, 3.1.2, 4.0.0
• Fix Version/s: 4.0.0-alpha-1
• Component/s: UDF
• Labels: backward-incompatible pull-request-available timestamp
• Target Version/s: 4.0.0
• Assignee: Nikhil Gupta
• Reporter: Nikhil Gupta
• Created: 18/Jun/21 16:49
• Updated: 08/Sep/23 08:14
• Resolved: 28/Jun/21 04:28

Description

Hive 1.2.1:

 select date_format('1400-01-14 01:00:00', 'yyyy-MM-dd HH:mm:ss z');
+--------------------------+--+
|           _c0            |
+--------------------------+--+
| 1400-01-14 01:00:00 ICT  |
+--------------------------+--+

select date_format('1800-01-14 01:00:00', 'yyyy-MM-dd HH:mm:ss z');
+--------------------------+--+
|           _c0            |
+--------------------------+--+
| 1800-01-14 01:00:00 ICT  |
+--------------------------+--+

Hive 3.1, Hive 4.0:

select date_format('1400-01-14 01:00:00', 'yyyy-MM-dd HH:mm:ss z');
+--------------------------+
|           _c0            |
+--------------------------+
| 1400-01-06 01:17:56 ICT  |
+--------------------------+

select date_format('1800-01-14 01:00:00', 'yyyy-MM-dd HH:mm:ss z');
+--------------------------+
|           _c0            |
+--------------------------+
| 1800-01-14 01:17:56 ICT  |
+--------------------------+

VM timezone is set to 'Asia/Bangkok'

Issue Links

• depends upon: HIVE-25093 date_format() UDF is returning output in UTC time zone only
• is caused by: HIVE-12192 Hive should carry out timestamp computations in UTC
• is caused by: HIVE-20007 Hive should carry out timestamp computations in UTC
• is related to: HIVE-27673 Configurable datetime formatter for date_format
• links to: GitHub Pull Request #2409