How can i add days to a Hive timestamp without loosing hours, minutes and seconds

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

Answer

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