Re: how to properly filter a dataset by dates ?
I finally settled for: dataset= dataset.where(to_date( dataset.col("Date"),"MM-dd-").geq(new java.sql.Date(new SimpleDateFormat("MM-dd-").parse("02-03-2012").getTime(; which seems overly complicated, I was hoping for a simpler Spark solution. Anyways thanks guys! Le ven. 17 juin 2022 à 22:35, marc nicole a écrit : > String dateString = String.format("%d-%02d-%02d", 2012, 02, 03); > Date sqlDate = java.sql.Date.valueOf(dateString); > dataset= > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq(sqlDate)); > > Is the only way I found to make it work, I am sure there's better than > this > > Le ven. 17 juin 2022 à 22:13, marc nicole a écrit : > >> @Stelios : to_date requires column type >> @Sean how to parse a literal to a date lit("02-03-2012").cast("date")? >> >> Le ven. 17 juin 2022 à 22:07, Stelios Philippou a >> écrit : >> >>> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq(to_date("02-03-2012", >>> "MM-dd-")); >>> >>> On Fri, 17 Jun 2022, 22:51 marc nicole, wrote: >>> dataset = dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); ? This is returning an empty dataset. Le ven. 17 juin 2022 à 21:34, Stelios Philippou a écrit : > You are already doing it once. > to_date the second part and don't forget to cast it as well > > On Fri, 17 Jun 2022, 22:08 marc nicole, wrote: > >> should i cast to date the target date then? for example maybe: >> >> dataset = >>> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); >>> ? >> >> How to to do that ? comparing with dates? >> >> >> Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : >> >>> Look at your query again. You are comparing dates to strings. The >>> dates widen back to strings. >>> >>> On Fri, Jun 17, 2022, 1:39 PM marc nicole >>> wrote: >>> I also tried: dataset = > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); But it returned an empty dataset. Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : > Same answer as last time - those are strings, not dates. > 02-02-2015 as a string is before 02-03-2012. > You apply date function to dates, not strings. > You have to parse the dates properly, which was the problem in > your last email. > > On Fri, Jun 17, 2022 at 12:58 PM marc nicole > wrote: > >> Hello, >> >> I have a dataset containing a column of dates, which I want to >> use for filtering. Nothing, from what I have tried, seems to return >> the >> exact right solution. >> Here's my input: >> >> + + >> |Date| >> ++ >> | 02-08-2019 | >> ++ >> | 02-07-2019 | >> ++ >> | 12-01-2019 | >> ++ >> | 02-02-2015 | >> ++ >> | 02-03-2012 | >> ++ >> | 05-06-2018 | >> ++ >> | 02-08-2022 | >> ++ >> >> The code that i have tried (always giving missing dates in the >> result): >> >> dataset = dataset.filter( >>> dataset.col("Date").geq("02-03-2012")); // not showing the date of >>> *02-02-2015* >> >> >> I tried to apply *date_trunc()* with the first parameter "day" >> but nothing. >> >> I have also compared a converted column (using *to_date()*) with >> a *literal *of the target date but always returning an empty >> dataset. >> >> How to do that in Java ? >> >>
Re: how to properly filter a dataset by dates ?
String dateString = String.format("%d-%02d-%02d", 2012, 02, 03); Date sqlDate = java.sql.Date.valueOf(dateString); dataset= dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq(sqlDate)); Is the only way I found to make it work, I am sure there's better than this Le ven. 17 juin 2022 à 22:13, marc nicole a écrit : > @Stelios : to_date requires column type > @Sean how to parse a literal to a date lit("02-03-2012").cast("date")? > > Le ven. 17 juin 2022 à 22:07, Stelios Philippou a > écrit : > >> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq(to_date("02-03-2012", >> "MM-dd-")); >> >> On Fri, 17 Jun 2022, 22:51 marc nicole, wrote: >> >>> dataset = >>> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); >>> ? >>> This is returning an empty dataset. >>> >>> Le ven. 17 juin 2022 à 21:34, Stelios Philippou a >>> écrit : >>> You are already doing it once. to_date the second part and don't forget to cast it as well On Fri, 17 Jun 2022, 22:08 marc nicole, wrote: > should i cast to date the target date then? for example maybe: > > dataset = >> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); >> ? > > How to to do that ? comparing with dates? > > > Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : > >> Look at your query again. You are comparing dates to strings. The >> dates widen back to strings. >> >> On Fri, Jun 17, 2022, 1:39 PM marc nicole >> wrote: >> >>> I also tried: >>> >>> dataset = dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); >>> >>> >>> But it returned an empty dataset. >>> >>> Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : >>> Same answer as last time - those are strings, not dates. 02-02-2015 as a string is before 02-03-2012. You apply date function to dates, not strings. You have to parse the dates properly, which was the problem in your last email. On Fri, Jun 17, 2022 at 12:58 PM marc nicole wrote: > Hello, > > I have a dataset containing a column of dates, which I want to use > for filtering. Nothing, from what I have tried, seems to return the > exact > right solution. > Here's my input: > > + + > |Date| > ++ > | 02-08-2019 | > ++ > | 02-07-2019 | > ++ > | 12-01-2019 | > ++ > | 02-02-2015 | > ++ > | 02-03-2012 | > ++ > | 05-06-2018 | > ++ > | 02-08-2022 | > ++ > > The code that i have tried (always giving missing dates in the > result): > > dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); >> // not showing the date of *02-02-2015* > > > I tried to apply *date_trunc()* with the first parameter "day" > but nothing. > > I have also compared a converted column (using *to_date()*) with > a *literal *of the target date but always returning an empty > dataset. > > How to do that in Java ? > >
Re: how to properly filter a dataset by dates ?
@Stelios : to_date requires column type @Sean how to parse a literal to a date lit("02-03-2012").cast("date")? Le ven. 17 juin 2022 à 22:07, Stelios Philippou a écrit : > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq(to_date("02-03-2012", > "MM-dd-")); > > On Fri, 17 Jun 2022, 22:51 marc nicole, wrote: > >> dataset = >> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); >> ? >> This is returning an empty dataset. >> >> Le ven. 17 juin 2022 à 21:34, Stelios Philippou a >> écrit : >> >>> You are already doing it once. >>> to_date the second part and don't forget to cast it as well >>> >>> On Fri, 17 Jun 2022, 22:08 marc nicole, wrote: >>> should i cast to date the target date then? for example maybe: dataset = > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); > ? How to to do that ? comparing with dates? Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : > Look at your query again. You are comparing dates to strings. The > dates widen back to strings. > > On Fri, Jun 17, 2022, 1:39 PM marc nicole wrote: > >> I also tried: >> >> dataset = >>> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); >> >> >> But it returned an empty dataset. >> >> Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : >> >>> Same answer as last time - those are strings, not dates. 02-02-2015 >>> as a string is before 02-03-2012. >>> You apply date function to dates, not strings. >>> You have to parse the dates properly, which was the problem in your >>> last email. >>> >>> On Fri, Jun 17, 2022 at 12:58 PM marc nicole >>> wrote: >>> Hello, I have a dataset containing a column of dates, which I want to use for filtering. Nothing, from what I have tried, seems to return the exact right solution. Here's my input: + + |Date| ++ | 02-08-2019 | ++ | 02-07-2019 | ++ | 12-01-2019 | ++ | 02-02-2015 | ++ | 02-03-2012 | ++ | 05-06-2018 | ++ | 02-08-2022 | ++ The code that i have tried (always giving missing dates in the result): dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); > // not showing the date of *02-02-2015* I tried to apply *date_trunc()* with the first parameter "day" but nothing. I have also compared a converted column (using *to_date()*) with a *literal *of the target date but always returning an empty dataset. How to do that in Java ?
Re: how to properly filter a dataset by dates ?
dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq(to_date("02-03-2012", "MM-dd-")); On Fri, 17 Jun 2022, 22:51 marc nicole, wrote: > dataset = > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); > ? > This is returning an empty dataset. > > Le ven. 17 juin 2022 à 21:34, Stelios Philippou a > écrit : > >> You are already doing it once. >> to_date the second part and don't forget to cast it as well >> >> On Fri, 17 Jun 2022, 22:08 marc nicole, wrote: >> >>> should i cast to date the target date then? for example maybe: >>> >>> dataset = dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); ? >>> >>> How to to do that ? comparing with dates? >>> >>> >>> Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : >>> Look at your query again. You are comparing dates to strings. The dates widen back to strings. On Fri, Jun 17, 2022, 1:39 PM marc nicole wrote: > I also tried: > > dataset = >> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); > > > But it returned an empty dataset. > > Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : > >> Same answer as last time - those are strings, not dates. 02-02-2015 >> as a string is before 02-03-2012. >> You apply date function to dates, not strings. >> You have to parse the dates properly, which was the problem in your >> last email. >> >> On Fri, Jun 17, 2022 at 12:58 PM marc nicole >> wrote: >> >>> Hello, >>> >>> I have a dataset containing a column of dates, which I want to use >>> for filtering. Nothing, from what I have tried, seems to return the >>> exact >>> right solution. >>> Here's my input: >>> >>> + + >>> |Date| >>> ++ >>> | 02-08-2019 | >>> ++ >>> | 02-07-2019 | >>> ++ >>> | 12-01-2019 | >>> ++ >>> | 02-02-2015 | >>> ++ >>> | 02-03-2012 | >>> ++ >>> | 05-06-2018 | >>> ++ >>> | 02-08-2022 | >>> ++ >>> >>> The code that i have tried (always giving missing dates in the >>> result): >>> >>> dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // not showing the date of *02-02-2015* >>> >>> >>> I tried to apply *date_trunc()* with the first parameter "day" but >>> nothing. >>> >>> I have also compared a converted column (using *to_date()*) with a >>> *literal *of the target date but always returning an empty dataset. >>> >>> How to do that in Java ? >>> >>>
Re: how to properly filter a dataset by dates ?
dataset = dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); ? This is returning an empty dataset. Le ven. 17 juin 2022 à 21:34, Stelios Philippou a écrit : > You are already doing it once. > to_date the second part and don't forget to cast it as well > > On Fri, 17 Jun 2022, 22:08 marc nicole, wrote: > >> should i cast to date the target date then? for example maybe: >> >> dataset = >>> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); >>> ? >> >> How to to do that ? comparing with dates? >> >> >> Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : >> >>> Look at your query again. You are comparing dates to strings. The dates >>> widen back to strings. >>> >>> On Fri, Jun 17, 2022, 1:39 PM marc nicole wrote: >>> I also tried: dataset = > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); But it returned an empty dataset. Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : > Same answer as last time - those are strings, not dates. 02-02-2015 as > a string is before 02-03-2012. > You apply date function to dates, not strings. > You have to parse the dates properly, which was the problem in your > last email. > > On Fri, Jun 17, 2022 at 12:58 PM marc nicole > wrote: > >> Hello, >> >> I have a dataset containing a column of dates, which I want to use >> for filtering. Nothing, from what I have tried, seems to return the exact >> right solution. >> Here's my input: >> >> + + >> |Date| >> ++ >> | 02-08-2019 | >> ++ >> | 02-07-2019 | >> ++ >> | 12-01-2019 | >> ++ >> | 02-02-2015 | >> ++ >> | 02-03-2012 | >> ++ >> | 05-06-2018 | >> ++ >> | 02-08-2022 | >> ++ >> >> The code that i have tried (always giving missing dates in the >> result): >> >> dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // >>> not showing the date of *02-02-2015* >> >> >> I tried to apply *date_trunc()* with the first parameter "day" but >> nothing. >> >> I have also compared a converted column (using *to_date()*) with a >> *literal *of the target date but always returning an empty dataset. >> >> How to do that in Java ? >> >>
Re: how to properly filter a dataset by dates ?
You are already doing it once. to_date the second part and don't forget to cast it as well On Fri, 17 Jun 2022, 22:08 marc nicole, wrote: > should i cast to date the target date then? for example maybe: > > dataset = >> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); >> ? > > How to to do that ? comparing with dates? > > > Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : > >> Look at your query again. You are comparing dates to strings. The dates >> widen back to strings. >> >> On Fri, Jun 17, 2022, 1:39 PM marc nicole wrote: >> >>> I also tried: >>> >>> dataset = dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); >>> >>> >>> But it returned an empty dataset. >>> >>> Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : >>> Same answer as last time - those are strings, not dates. 02-02-2015 as a string is before 02-03-2012. You apply date function to dates, not strings. You have to parse the dates properly, which was the problem in your last email. On Fri, Jun 17, 2022 at 12:58 PM marc nicole wrote: > Hello, > > I have a dataset containing a column of dates, which I want to use for > filtering. Nothing, from what I have tried, seems to return the exact > right > solution. > Here's my input: > > + + > |Date| > ++ > | 02-08-2019 | > ++ > | 02-07-2019 | > ++ > | 12-01-2019 | > ++ > | 02-02-2015 | > ++ > | 02-03-2012 | > ++ > | 05-06-2018 | > ++ > | 02-08-2022 | > ++ > > The code that i have tried (always giving missing dates in the result): > > dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // >> not showing the date of *02-02-2015* > > > I tried to apply *date_trunc()* with the first parameter "day" but > nothing. > > I have also compared a converted column (using *to_date()*) with a > *literal *of the target date but always returning an empty dataset. > > How to do that in Java ? > >
Re: how to properly filter a dataset by dates ?
should i cast to date the target date then? for example maybe: dataset = > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012").cast("date")); > ? How to to do that ? comparing with dates? Le ven. 17 juin 2022 à 20:52, Sean Owen a écrit : > Look at your query again. You are comparing dates to strings. The dates > widen back to strings. > > On Fri, Jun 17, 2022, 1:39 PM marc nicole wrote: > >> I also tried: >> >> dataset = >>> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); >> >> >> But it returned an empty dataset. >> >> Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : >> >>> Same answer as last time - those are strings, not dates. 02-02-2015 as a >>> string is before 02-03-2012. >>> You apply date function to dates, not strings. >>> You have to parse the dates properly, which was the problem in your last >>> email. >>> >>> On Fri, Jun 17, 2022 at 12:58 PM marc nicole >>> wrote: >>> Hello, I have a dataset containing a column of dates, which I want to use for filtering. Nothing, from what I have tried, seems to return the exact right solution. Here's my input: + + |Date| ++ | 02-08-2019 | ++ | 02-07-2019 | ++ | 12-01-2019 | ++ | 02-02-2015 | ++ | 02-03-2012 | ++ | 05-06-2018 | ++ | 02-08-2022 | ++ The code that i have tried (always giving missing dates in the result): dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // > not showing the date of *02-02-2015* I tried to apply *date_trunc()* with the first parameter "day" but nothing. I have also compared a converted column (using *to_date()*) with a *literal *of the target date but always returning an empty dataset. How to do that in Java ?
Re: how to properly filter a dataset by dates ?
Look at your query again. You are comparing dates to strings. The dates widen back to strings. On Fri, Jun 17, 2022, 1:39 PM marc nicole wrote: > I also tried: > > dataset = >> dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); > > > But it returned an empty dataset. > > Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : > >> Same answer as last time - those are strings, not dates. 02-02-2015 as a >> string is before 02-03-2012. >> You apply date function to dates, not strings. >> You have to parse the dates properly, which was the problem in your last >> email. >> >> On Fri, Jun 17, 2022 at 12:58 PM marc nicole wrote: >> >>> Hello, >>> >>> I have a dataset containing a column of dates, which I want to use for >>> filtering. Nothing, from what I have tried, seems to return the exact right >>> solution. >>> Here's my input: >>> >>> + + >>> |Date| >>> ++ >>> | 02-08-2019 | >>> ++ >>> | 02-07-2019 | >>> ++ >>> | 12-01-2019 | >>> ++ >>> | 02-02-2015 | >>> ++ >>> | 02-03-2012 | >>> ++ >>> | 05-06-2018 | >>> ++ >>> | 02-08-2022 | >>> ++ >>> >>> The code that i have tried (always giving missing dates in the result): >>> >>> dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // not showing the date of *02-02-2015* >>> >>> >>> I tried to apply *date_trunc()* with the first parameter "day" but >>> nothing. >>> >>> I have also compared a converted column (using *to_date()*) with a >>> *literal *of the target date but always returning an empty dataset. >>> >>> How to do that in Java ? >>> >>>
Re: how to properly filter a dataset by dates ?
I also tried: dataset = > dataset.where(to_date(dataset.col("Date"),"MM-dd-").geq("02-03-2012")); But it returned an empty dataset. Le ven. 17 juin 2022 à 20:28, Sean Owen a écrit : > Same answer as last time - those are strings, not dates. 02-02-2015 as a > string is before 02-03-2012. > You apply date function to dates, not strings. > You have to parse the dates properly, which was the problem in your last > email. > > On Fri, Jun 17, 2022 at 12:58 PM marc nicole wrote: > >> Hello, >> >> I have a dataset containing a column of dates, which I want to use for >> filtering. Nothing, from what I have tried, seems to return the exact right >> solution. >> Here's my input: >> >> + + >> |Date| >> ++ >> | 02-08-2019 | >> ++ >> | 02-07-2019 | >> ++ >> | 12-01-2019 | >> ++ >> | 02-02-2015 | >> ++ >> | 02-03-2012 | >> ++ >> | 05-06-2018 | >> ++ >> | 02-08-2022 | >> ++ >> >> The code that i have tried (always giving missing dates in the result): >> >> dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // not >>> showing the date of *02-02-2015* >> >> >> I tried to apply *date_trunc()* with the first parameter "day" but >> nothing. >> >> I have also compared a converted column (using *to_date()*) with a >> *literal *of the target date but always returning an empty dataset. >> >> How to do that in Java ? >> >>
Re: how to properly filter a dataset by dates ?
Same answer as last time - those are strings, not dates. 02-02-2015 as a string is before 02-03-2012. You apply date function to dates, not strings. You have to parse the dates properly, which was the problem in your last email. On Fri, Jun 17, 2022 at 12:58 PM marc nicole wrote: > Hello, > > I have a dataset containing a column of dates, which I want to use for > filtering. Nothing, from what I have tried, seems to return the exact right > solution. > Here's my input: > > + + > |Date| > ++ > | 02-08-2019 | > ++ > | 02-07-2019 | > ++ > | 12-01-2019 | > ++ > | 02-02-2015 | > ++ > | 02-03-2012 | > ++ > | 05-06-2018 | > ++ > | 02-08-2022 | > ++ > > The code that i have tried (always giving missing dates in the result): > > dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // not >> showing the date of *02-02-2015* > > > I tried to apply *date_trunc()* with the first parameter "day" but > nothing. > > I have also compared a converted column (using *to_date()*) with a > *literal *of the target date but always returning an empty dataset. > > How to do that in Java ? > >
how to properly filter a dataset by dates ?
Hello, I have a dataset containing a column of dates, which I want to use for filtering. Nothing, from what I have tried, seems to return the exact right solution. Here's my input: + + |Date| ++ | 02-08-2019 | ++ | 02-07-2019 | ++ | 12-01-2019 | ++ | 02-02-2015 | ++ | 02-03-2012 | ++ | 05-06-2018 | ++ | 02-08-2022 | ++ The code that i have tried (always giving missing dates in the result): dataset = dataset.filter( dataset.col("Date").geq("02-03-2012")); // not > showing the date of *02-02-2015* I tried to apply *date_trunc()* with the first parameter "day" but nothing. I have also compared a converted column (using *to_date()*) with a *literal *of the target date but always returning an empty dataset. How to do that in Java ?