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 ?
>
>
