Re: Timestamp Difference/operations

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

Re: Timestamp Difference/operations

John Zhuge-2
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

Srabasti Banerjee
Hi Paras,

Looks like you can get the difference in seconds as well.
Hopefully this helps! 
Are you looking for a specific usecase? Can you please elaborate with an example?

Thanks 
Srabasti Banerjee 



On Sun, Oct 14, 2018 at 23:41, 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

Paras Agarwal

Thanks Srabasti,


I am trying to convert teradata to spark sql.


TERADATA:
select * from Table1 where Date '1974-01-02' > CAST(birth_date AS TIMESTAMP(0)) + (TIME '12:34:34' - TIME '00:00:00' HOUR TO SECOND);

HIVE ( With some tweaks i can write):
SELECT * FROM foodmart.trimmed_employee WHERE Date '1974-01-02' > CAST(CAST(CURRENT_DATE AS TIMESTAMP) + (CAST('2000-01-01 12:34:34' AS TIMESTAMP) - (CAST('2000-01-01 00:00:00' AS TIMESTAMP))) AS DATE)

SPARK (SO need spark equivalent):

SELECT * FROM foodmart.trimmed_employee WHERE Date '1974-01-02' > CAST(CAST(CURRENT_DATE AS TIMESTAMP) + (??) AS DATE)


Need to fill above ?? so that i can process.


Thanks & Regards,

Paras

9130006036


From: Srabasti Banerjee <[hidden email]>
Sent: Tuesday, October 16, 2018 6:45:26 AM
To: Paras Agarwal; John Zhuge
Cc: user; dev
Subject: Re: Timestamp Difference/operations
 
Hi Paras,

Looks like you can get the difference in seconds as well.
Hopefully this helps! 
Are you looking for a specific usecase? Can you please elaborate with an example?

Thanks 
Srabasti Banerjee 



On Sun, Oct 14, 2018 at 23:41, 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