Selector for birth date

[Krlos]

Hello,

I suspect that this is very easy, but I can not find the solution.

I have created a custom field birth_date in my users template.

I'm trying to output users that are celabrating her/his birthday today, but I can't find a selector to do the job.

I was checking the data stored in the custom field and is like this: 1992-10-23 00:00:00

I'm using this selector:

$result = $users->find("birth_date~=today");

when I use that selector, it just output all users.

 

[elabx]

Doesn't using the "=" operator work? Why use "~="? Maybe I'm not catching something.

[Krlos]

2 minutes ago, elabx said:

Doesn't using the "=" operator work? Why use "~="? Maybe I'm not catching something.

I have tried all selector operators, none of them work for me.

[elabx]

Ohh! And just read it's a custom field! I was assuming a date field and I do wonder, why not use the default date field for this? I think the selector might not be working because it's not getting saved as a timestamp.

[Krlos]

6 minutes ago, elabx said:

Ohh! And just read it's a custom field! I was assuming a date field and I do wonder, why not use the default date field for this?

I'm using default datetime field.

In my users template I have:

but in the custom field is stored:

I guess if I construct a date like (month-day):

$monthAndDay = "11-18"

should match 1962-11-18 00:00:00

but using selector (or any other operator):

$result = $users->find("birth_date~=monthAndDay");

it just output all users.

[maxf5]

Dates are stored as timestamps and you have to search the whole day :)

$start = strtotime(date('Y-m-d') . " 00:00:00");
$end = strtotime(date('Y-m-d') . " 23:59:59");
$result = $users->find("birth_date>=$start, birth_date<=$end");

 

[kongondo]

56 minutes ago, maxf5 said:

Dates are stored as timestamps...

I don't think this is correct. If you are talking about FieldtypeDatetime, dates are stored as datetime. This is not the same as a timestamp ?; there are some 'little' differences. Sorry if I am being pedantic!

Edited October 23, 2018 by kongondo

[BitPoet]

A little self promotion: I just added "date" and "time" subfields to my DatetimeAdvanced replacement for PW's core Datetime field. That lets you search like this:

$today = date('y-m-d');
$result = $users->find("birth_date.date=$today");

Also works for in-memory (i.e. PageArray) searches.

[maxf5]

14 hours ago, kongondo said:

I don't think this is correct. If you are talking about FieldtypeDatetime, dates are stored as datetime. This is not the same as a timestamp ?; there are some 'little' differences. Sorry if I am being pedantic!

no problem, thanks for clearing up.

I thought i would be the same as $page->created, $page->modified

[Krlos]

On 10/23/2018 at 2:45 PM, BitPoet said:

A little self promotion: I just added "date" and "time" subfields to my DatetimeAdvanced replacement for PW's core Datetime field. That lets you search like this:

$today = date('y-m-d');
$result = $users->find("birth_date.date=$today");

Also works for in-memory (i.e. PageArray) searches.

Thank you! this is working great.

But now I have request, how can I order based on day of birth.

I have this selector:

$result = $users->find("profile_birth_date.month=$mes, sort=profile_birth_date");

but is not sorting.

I tried adding .day to the profile_birth_date selector

but I got this error:

Error: Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column '_sort_profile_birth_date_day.day' in 'order clause' (in /home/xxxx/xxxx/wire/core/PageFinder.php line 413)

 

[BitPoet]

I'm not sure if I can implement sorting directly in the database for advanced date parts, but you can sort in-memory.

$result = $users->find("profile_birth_date.month=$mes");
$result->sort('profile_birth_date.day');
// $result is now sorted

 

[Krlos]

3 hours ago, BitPoet said:

I'm not sure if I can implement sorting directly in the database for advanced date parts, but you can sort in-memory.

$result = $users->find("profile_birth_date.month=$mes");
$result->sort('profile_birth_date.day');
// $result is now sorted

 

Thank you @BitPoet

It worked perfectly!