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
Hi Paras,Check out the link 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
On Sun, Oct 14, 2018 at 23:41, Paras Agarwal
wrote: #yiv8627769989 #yiv8627769989 -- P
{margin-top:0;margin-bottom:0;}#yiv8627769989
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
Timestamp Difference/operations
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
