[HIVE-25093] date_format() UDF is returning output in UTC time zone only

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 3.1.2
• Fix Version/s: 4.0.0-alpha-1
• Component/s: UDF
• Labels: pull-request-available timestamp
• Target Version/s: 4.0.0
• Assignee: Ashish Sharma
• Reporter: Ashish Sharma
• Created: 05/May/21 07:11
• Updated: 29/Nov/22 10:02
• Resolved: 14/Jun/21 13:57

Description

HIVE - 1.2

sshuser@hn0-dateti:~$ timedatectl

Local time: Thu 2021-05-06 11:56:08 IST
Universal time: Thu 2021-05-06 06:26:08 UTC
RTC time: Thu 2021-05-06 06:26:08
Time zone: Asia/Kolkata (IST, +0530)
Network time on: yes
NTP synchronized: yes
RTC in local TZ: no

sshuser@hn0-dateti:~$ beeline
0: jdbc:hive2://localhost:10001/default> select date_format(current_timestamp,"yyyy-MM-dd HH:mm:ss.SSS z");
------------------------------+

_c0

------------------------------+

2021-05-06 11:58:53.760 IST

------------------------------+
1 row selected (1.271 seconds)

HIVE - 3.1.0

sshuser@hn0-testja:~$ timedatectl
Local time: Thu 2021-05-06 12:03:32 IST
Universal time: Thu 2021-05-06 06:33:32 UTC
RTC time: Thu 2021-05-06 06:33:32
Time zone: Asia/Kolkata (IST, +0530)
Network time on: yes
NTP synchronized: yes
RTC in local TZ: no

sshuser@hn0-testja:~$ beeline
0: jdbc:hive2://zk0-testja.e0mrrixnyxde5h1suy> select date_format(current_timestamp,"yyyy-MM-dd HH:mm:ss.SSS z");
------------------------------

_c0

------------------------------

2021-05-06 06:33:59.078 UTC

------------------------------
1 row selected (13.396 seconds)

0: jdbc:hive2://zk0-testja.e0mrrixnyxde5h1suy> set hive.local.time.zone=Asia/Kolkata;
No rows affected (0.025 seconds)
0: jdbc:hive2://zk0-testja.e0mrrixnyxde5h1suy> select date_format(current_timestamp,"yyyy-MM-dd HH:mm:ss.SSS z");
------------------------------

_c0

------------------------------

2021-05-06 12:08:15.118 UTC

------------------------------
1 row selected (1.074 seconds)

expected result was 2021-05-06 12:08:15.118 IST

As part of HIVE-12192 it was decided to have a common time zone for all computation i.e. "UTC". Due to which data_format() function was hard coded to "UTC".

But later in HIVE-21039 it was decided that user session time zone value should be the default not UTC.

date_format() was not fixed as part of HIVE-21039.

what should be the ideal time zone value of date_format().

Issue Links

• Is contained by: HIVE-26751 Bug Fixes and Improvements for 3.2.0 release
• is depended upon by: HIVE-25268 date_format udf returns wrong results for dates prior to 1900 if the local timezone is other than UTC
• relates to: HIVE-25250 Fix TestHS2ImpersonationWithRemoteMS.testImpersonation
• links to: GitHub Pull Request #2252
• links to: GitHub Pull Request #2252