Support Questions
Find answers, ask questions, and share your expertise

How to substract minutes

New Contributor

Hi,

I have a start time and an end time that I need to subtract (I really need to substract only a few minutes). I try to use the following functions but the result is NULL:

SELECT minutes_sub(to_timestamp(t.horafin,"mmss"), minute(to_timestamp(t.horaini,"mmss")) )

  FROM table t;

 

       t.horaini, t.horafin are string

       With my data:

       to_timestamp(t.horafin,"mmss") = 00:17:03

       to_timestamp(t.horaini,"mmss") = 00:16:06

       minute(to_timestamp(t.horaini,"mmss")) = 6

      .... and minutes_sub(to_timestamp(t.horafin,"mmss"), minute(to_timestamp(t.horaini,"mmss")) ) = NULL

     ... and I need 00:00:57

 

  If I use the function: datediff(to_timestamp(t.horafin,"mmss"), to_timestamp(t.horaini,"mmss")) the result is also NULL.

Can you help me please?

2 REPLIES 2

Contributor

@ana24 

 

You can make use Hive CAST function to find the timestamp difference.Please refer this article and frame your queries according to your case.

 

http://sqlandhadoop.com/how-to-subtract-timestamp-date-time-in-hive/

 

New Contributor

It is not working! If I write only this sentence:

 

SELECT UNIX_TIMESTAMP(‘17:30:12’, ‘HH:mm:ss’)

 

the return is NULL!!

 

; ;