[
https://issues.apache.org/jira/browse/HIVE-345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13471395#comment-13471395
]
Mark Grover commented on HIVE-345:
----------------------------------
Presently Hive doesn't seem to provide functionality to specify the timezone
when performing date/time conversions. The pre-set timezone is used by default.
I would like to propose addition of such functionality (by means of this JIRA)
that allows users to optionally specify a timezone when performing date/time
conversions.
There are 2 ways I can think of implementing this:
1) Add an optional parameter to functions like from_unixtime and unix_timestamp
to specify the timezone to be used during conversion.
2) Create a new UDF, convert_tz with a prototype: CONVERT_TZ(dt,src_tz,dest_tz)
that will perform the timezone conversion. In this case, folks would have to
convert the argument's timezone before passing it in to unix_timestamp() or
convert the result's timezone when using from_unixtime().
Advantage of the first approach is that it provides a direct way of performing
date/time conversions with a particular timezone. Disadvantage is that there is
a risk of confusing users. For example, from_unixtime takes a 2nd optional
parameter that be used to specify the format. If we go with approach one and
make timezone third parameter, we force people to specify a format (which is a
string just like timezone) if they wish you to use the timezone parameter.
Advantage of the second approach is that it's a more generic method of
converting timezones and can potentially be used with other date/time related
UDFs. It's also similar to MySQL does (MySQL doesn't have parameters in
from_unixtime and unix_timestamp to specify timezone). Disadvantage is that
people using from_unixtime and unix_timestamp would have to make a nested UDF
call e.g. convert_tz(from_unixtime()) or unix_timestamp(convert_tz()) in order
to accomplish what they want.
Based on the above analysis, I am inclined towards the second approach (i.e.
creating a convert_tz UDF). What do you folks think? Anyone out there that
prefers approach 1 or a completely different approach?
> Extend Date UDFs to support time zone and full specs as in MySQL
> ----------------------------------------------------------------
>
> Key: HIVE-345
> URL: https://issues.apache.org/jira/browse/HIVE-345
> Project: Hive
> Issue Type: Improvement
> Components: UDF
> Affects Versions: 0.3.0
> Reporter: Zheng Shao
> Assignee: Mark Grover
>
> Most of the Date UDF in Hive now are based on String instead of Date objects,
> and they have limited functionality compared with MySQL.
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff
> We should make it fully compliant with what MySQL offers.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
