[
https://issues.apache.org/jira/browse/HIVE-25093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17350053#comment-17350053
]
Ashish Sharma edited comment on HIVE-25093 at 5/23/21, 3:01 PM:
----------------------------------------------------------------
[~zabetak]
shuser@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
Please checkout the local time and UTC time value in "timedatectl"
0: jdbc:hive2://zk0-testja.e0mrrixnyxde5h1suy> *select
date_format(current_timestamp,"yyyy-MM-dd HH:mm:ss.SSS z");*
------------------------------
_c0
------------------------------
*{color:red}2021-05-06 12:08:15.118 UTC{color}*
------------------------------
1 row selected (1.074 seconds)
So the output of the query is *2021-05-06 12:08:15.118 UTC* . So the problem
here is if _date_format _is compliment to UTC time then output should be
*2021-05-06 06:33:59.078 UTC* or if it compliment to "hive.local.time.zone"
then result should be *2021-05-06 12:08:15.118 IST*. Not the combination of
both.
Output of "current_timestamp" depends upon config "hive.local.time.zone" which
is set of IST in the given example. So the input to date_format is given is
*2021-05-06 12:08:15.118 IST* if the result is present in UTC then result
should be *2021-05-06 06:33:59.078 UTC* not *2021-05-06 12:08:15.118 UTC*.
Also interpretation of 'z' in "yyyy-MM-dd HH:mm:ss.SSS z" is local time zone.
_date_format _ use SimpleDateFormat. Please check out the official
documentation
https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
was (Author: ashish-kumar-sharma):
[~zabetak]
shuser@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
Please checkout the local time and UTC time value in "timedatectl"
0: jdbc:hive2://zk0-testja.e0mrrixnyxde5h1suy> *select
date_format(current_timestamp,"yyyy-MM-dd HH:mm:ss.SSS z");*
------------------------------
_c0
------------------------------
*{color:red}2021-05-06 12:08:15.118 UTC{color}*
------------------------------
1 row selected (1.074 seconds)
So the output of the query is *{color:red}2021-05-06 12:08:15.118 UTC{color}* .
So the problem here is if _date_format _is compliment to UTC time then output
should be *{color:red}2021-05-06 06:33:59.078 UTC{color}* or if it compliment
to "hive.local.time.zone" then result should be *{color:red}2021-05-06
12:08:15.118 IST{color}*. Not the combination of both.
Output of "current_timestamp" depends upon config "hive.local.time.zone" which
is set of IST in the given example. So the input to date_format is given is
*{color:red}2021-05-06 12:08:15.118 IST{color}* if the result is present in UTC
then result should be *{color:red}2021-05-06 06:33:59.078 UTC{color}* not
*{color:red}2021-05-06 12:08:15.118 UTC{color}*.
Also interpretation of 'z' in "yyyy-MM-dd HH:mm:ss.SSS z" is local time zone.
_date_format _ use SimpleDateFormat. Please check out the official
documentation
https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
> date_format() UDF is returning values in UTC time zone only
> ------------------------------------------------------------
>
> Key: HIVE-25093
> URL: https://issues.apache.org/jira/browse/HIVE-25093
> Project: Hive
> Issue Type: Bug
> Components: UDF
> Affects Versions: 3.1.2
> Reporter: Ashish Sharma
> Assignee: Ashish Sharma
> Priority: Minor
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> *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 |
> +------------------------------+
> | *{color:red}2021-05-06 12:08:15.118 UTC{color}* |
> +------------------------------+
> 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().
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
