Timestamp Difference/operations

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Timestamp Difference/operations

Paras Agarwal

Hello Spark Community,

Currently in hive we can do operations on Timestamp Like :
CAST('2000-01-01 12:34:34' AS TIMESTAMP) - CAST('2000-01-01 00:00:00' AS TIMESTAMP)

Seems its not supporting in spark.
Is there any way available.

Kindly provide some insight on this.


Paras
9130006036

Reply | Threaded
Open this post in threaded view
|

Re: Timestamp Difference/operations

Paras Agarwal

Thanks John,


Actually need full date and  time difference not just date difference,

which I guess not supported.


Let me know if its possible, or any UDF available for the same.


Thanks And Regards,

Paras


From: John Zhuge <[hidden email]>
Sent: Friday, October 12, 2018 9:48:47 PM
To: Paras Agarwal
Cc: user; dev
Subject: Re: Timestamp Difference/operations
 
Yeah, operator "-" does not seem to be supported, however, you can use "datediff" function:

In [9]: select datediff(CAST('2000-02-01 12:34:34' AS TIMESTAMP), CAST('2000-01-01 00:00:00' AS TIMESTAMP))
Out[9]:
+----------------------------------------------------------------------------------------------------------------------+
| datediff(CAST(CAST(2000-02-01 12:34:34 AS TIMESTAMP) AS DATE), CAST(CAST(2000-01-01 00:00:00 AS TIMESTAMP) AS DATE)) |
+----------------------------------------------------------------------------------------------------------------------+
| 31                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+

In [10]: select datediff('2000-02-01 12:34:34', '2000-01-01 00:00:00')
Out[10]:
+--------------------------------------------------------------------------------+
| datediff(CAST(2000-02-01 12:34:34 AS DATE), CAST(2000-01-01 00:00:00 AS DATE)) |
+--------------------------------------------------------------------------------+
| 31                                                                             |
+--------------------------------------------------------------------------------+

In [11]: select datediff(timestamp '2000-02-01 12:34:34', timestamp '2000-01-01 00:00:00')
Out[11]:
+--------------------------------------------------------------------------------------------------------------+
| datediff(CAST(TIMESTAMP('2000-02-01 12:34:34.0') AS DATE), CAST(TIMESTAMP('2000-01-01 00:00:00.0') AS DATE)) |
+--------------------------------------------------------------------------------------------------------------+
| 31                                                                                                           |
+--------------------------------------------------------------------------------------------------------------+

On Fri, Oct 12, 2018 at 7:01 AM Paras Agarwal <[hidden email]> wrote:

Hello Spark Community,

Currently in hive we can do operations on Timestamp Like :
CAST('2000-01-01 12:34:34' AS TIMESTAMP) - CAST('2000-01-01 00:00:00' AS TIMESTAMP)

Seems its not supporting in spark.
Is there any way available.

Kindly provide some insight on this.


Paras
9130006036



--
John
Reply | Threaded
Open this post in threaded view
|

Re: Timestamp Difference/operations

Brandon Geise

How about

 

select unix_timestamp(timestamp2) – unix_timestamp(timestamp1)?

 

From: Paras Agarwal <[hidden email]>
Date: Monday, October 15, 2018 at 2:41 AM
To: John Zhuge <[hidden email]>
Cc: user <[hidden email]>, dev <[hidden email]>
Subject: Re: Timestamp Difference/operations

 

Thanks John,

 

Actually need full date and  time difference not just date difference,

which I guess not supported.

 

Let me know if its possible, or any UDF available for the same.

 

Thanks And Regards,

Paras


From: John Zhuge <[hidden email]>
Sent: Friday, October 12, 2018 9:48:47 PM
To: Paras Agarwal
Cc: user; dev
Subject: Re: Timestamp Difference/operations

 

Yeah, operator "-" does not seem to be supported, however, you can use "datediff" function:

 

In [9]: select datediff(CAST('2000-02-01 12:34:34' AS TIMESTAMP), CAST('2000-01-01 00:00:00' AS TIMESTAMP))

Out[9]:

+----------------------------------------------------------------------------------------------------------------------+

| datediff(CAST(CAST(2000-02-01 12:34:34 AS TIMESTAMP) AS DATE), CAST(CAST(2000-01-01 00:00:00 AS TIMESTAMP) AS DATE)) |

+----------------------------------------------------------------------------------------------------------------------+

| 31                                                                                                                   |

+----------------------------------------------------------------------------------------------------------------------+

 

In [10]: select datediff('2000-02-01 12:34:34', '2000-01-01 00:00:00')

Out[10]:

+--------------------------------------------------------------------------------+

| datediff(CAST(2000-02-01 12:34:34 AS DATE), CAST(2000-01-01 00:00:00 AS DATE)) |

+--------------------------------------------------------------------------------+

| 31                                                                             |

+--------------------------------------------------------------------------------+

 

In [11]: select datediff(timestamp '2000-02-01 12:34:34', timestamp '2000-01-01 00:00:00')

Out[11]:

+--------------------------------------------------------------------------------------------------------------+

| datediff(CAST(TIMESTAMP('2000-02-01 12:34:34.0') AS DATE), CAST(TIMESTAMP('2000-01-01 00:00:00.0') AS DATE)) |

+--------------------------------------------------------------------------------------------------------------+

| 31                                                                                                           |

+--------------------------------------------------------------------------------------------------------------+

 

On Fri, Oct 12, 2018 at 7:01 AM Paras Agarwal <[hidden email]> wrote:

Hello Spark Community,

Currently in hive we can do operations on Timestamp Like :
CAST('2000-01-01 12:34:34' AS TIMESTAMP) - CAST('2000-01-01 00:00:00' AS TIMESTAMP)

Seems its not supporting in spark.
Is there any way available.

Kindly provide some insight on this.


Paras
9130006036


 

--

John