2

I am using Hive 2.6.5 and when i want to add days to my timestamp, it doesn't keep the hours, minutes and seconds.

Exemple

SELECT from_unixtime(unix_timestamp(date_add("2021-01-15 09:34:21",2),'yyyyMMdd'),'yyyy-MM-dd HH:mm:ss');

in addition to that it returns a wrong result as :

2020-12-01 **00:00:00**

I would like it to return the value 2021-01-17 09:34:21

Thank you

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Azo
  • 67
  • 6
  • wouldn't that be because your use of unix_timestamp doesn't include the time component so it's truncating the time when it's being cast? or consider: https://stackoverflow.com/questions/31701847/hives-unix-timestamp-and-from-unixtime-functions or https://stackoverflow.com/questions/30399544/add-minutes-to-datetime-in-hive – xQbert Jan 20 '22 at 13:16
  • 1
    It seems like the function `add_date` truncates the hours,minutes and seconds. However I don't know how to add days to a date with the format `yyyy-MM-dd HH:mm:ss` without losing the `HH:m:ss` part. – Azo Jan 20 '22 at 14:06

1 Answers1

3
  1. date_add truncates
  2. Unnecessary unix_timestamp+from_unixtime conversion

Convert to timestamp, add interval:

SELECT timestamp("2021-01-15 09:34:21") + interval '2' day;

Result:

2021-01-17 09:34:21.0

Timestamp is displayed with zero millisecond part, it is default timestamp representation. If you want it as string without milliseconds, format using date_format or simply take substr()

SELECT date_format(timestamp("2021-01-15 09:34:21") + interval '2' day,'yyyy-MM-dd HH:mm:ss')

Result:

2021-01-17 09:34:21

And the same using substr:

SELECT substr(timestamp("2021-01-15 09:34:21") + interval '2' day,1,19)

If you need to calculate interval dynamically and your Hive version does not support it, see this example:

with mytable as (
select timestamp("2021-01-15 09:34:21") ts, 2 d
)
SELECT from_unixtime(unix_timestamp(ts) + (d*24*60*60))
from mytable

Result:

2021-01-17 09:34:21
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • 1
    It works great. I just added simple quote to the day's number : `SELECT date_format(timestamp("2021-01-15 09:34:21") + INTERVAL '2' DAY,'yyyy-MM-dd HH:mm:ss');` – Azo Jan 20 '22 at 16:59
  • @Azo Hmm... It worked without quotes on demo.gethue.com. Added quotes. – leftjoin Jan 20 '22 at 17:01
  • Can the value of Interval be a value of a column ? for exemple : SELECT myTable.col1, date_format(timestamp("2021-01-15 09:34:21") + INTERVAL myTable.col1 DAY,'yyyy-MM-dd HH:mm:ss') FROM myTable ; – Azo Jan 20 '22 at 17:26
  • @Azo Docs says it is possible since Hive 2.2.0 https://cwiki.apache.org/confluence/display/hive/languagemanual+types – leftjoin Jan 20 '22 at 17:45
  • 1
    @Azo If it does not work in your Hive then convert to unix_timestamp (returns seconds), add interval in seconds (days* 24*60*60), convert result to timestamp (from_unixtime) – leftjoin Jan 20 '22 at 17:51
  • 1
    Actually I made a mistake in the description, I work with Hive 1.2. So the second method with adding the interval as seconds works fine. Thanks! – Azo Jan 21 '22 at 09:01