Re: Timestamp Difference/operations
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 Sent: Tuesday, October 16, 2018 6:45:26 AM To: Paras Agarwal; John Zhuge Cc: user; dev Subject: Re: Timestamp Difference/operations Hi Paras, Check out the link Spark Scala: DateDiff of two columns by hour or minute<https://stackoverflow.com/questions/37058016/spark-scala-datediff-of-two-columns-by-hour-or-minute> <https://stackoverflow.com/questions/37058016/spark-scala-datediff-of-two-columns-by-hour-or-minute> Spark Scala: DateDiff of two columns by hour or minute I have two timestamp columns in a dataframe that I'd like to get the minute difference of, or alternatively, the hour difference of. Currently I'm able to get the day difference, with rounding, by ... 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 Sent from Yahoo Mail on Android<https://go.onelink.me/107872968?pid=InProduct=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers_wl=ym_sub1=Internal_sub2=Global_YGrowth_sub3=EmailSignature> On Sun, Oct 14, 2018 at 23:41, Paras Agarwal wrote: 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 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 mailto:paras.agar...@datametica.com>> 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
Re: Timestamp Difference/operations
How about select unix_timestamp(timestamp2) – unix_timestamp(timestamp1)? From: Paras Agarwal Date: Monday, October 15, 2018 at 2:41 AM To: John Zhuge Cc: user , dev 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 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 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
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 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 mailto:paras.agar...@datametica.com>> 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
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 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