Upcoming birthdays

[Jim S]

Hopefully I'm missing something obvious.

I have a birth date in my employee table.  I want to build a DAL query to show people who will have a birthday in the next month.

db.define_table('employee',

                Field('id', 'id', readable=False),

                Field('first_name', length=25, required=True),

                Field('last_name', length=25, required=True),

                Field('dob', 'date', label='Date of Birth'),

                format='%(last_name)s, %(first_name)s')

How do I write a query to show the employees with birthdays in the next 30 days?

-Jim

[黄祥]

perhaps you can use python datetime module.

e.g.

import datetime

print (datetime.date.today() + datetime.timedelta(1*365/12)).isoformat()

or

from datetime import date

from dateutil.relativedelta import relativedelta

six_months = date.today() + relativedelta(months=+1)

The advantage of the latest approach is that it takes care of issues with 28, 30, 31 days etc.

best regards,

stifan

[Jim S]

Stifan

I use that method to build dates quite often.  However, I don't know how to translate that into a DAL query.

-Jim

[Dave S]

On Tuesday, October 3, 2017 at 2:38:38 PM UTC-7, Jim S wrote:

Stifan

I use that method to build dates quite often.  However, I don't know how to translate that into a DAL query.

-Jim

I have an example, but it's at home.

/dps
 

[Limedrop]

It just works.  For example:

    import datetime

    now = datetime.datetime.now() 

    last_date = now + datetime.timedelta(days=settings.days_to_include)

    query = (db.person.interview_datetime < last_date)

    rows = db(query).select()

[Jim Steil]

I don't think that will do it. The date stored in the database would be 10/15/1980, or 10/21/1993.

Those should both show a birthday in the month of August. But, I don't want just a matching month. I want it for the next 30 days. 

So, on December 15th I should see all birthdays between December 15th and January 15th regardless of what year the birthday is in.

Jim

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/-mKujQj0oB4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dave S]

On Tuesday, October 3, 2017 at 4:48:52 PM UTC-7, Jim S wrote:

I don't think that will do it. The date stored in the database would be 10/15/1980, or 10/21/1993.

Those should both show a birthday in the month of August.

Er, October?
 

But, I don't want just a matching month. I want it for the next 30 days. 

So, on December 15th I should see all birthdays between December 15th and January 15th regardless of what year the birthday is in.

Jim

It's not too much more.  You identify the current day and month, and the day and month of 30 days from now, and do 2 selects:
First for the bdays whose month matches the current and whose day is greater than today.
Second for the bdays whose month matches the successor month and whose day is less than the +30 day.

(My example covers years from 1600 to present, for composers.  i also have getting everybody in any single calendar month, which is just one query.  Results for October:

Paul Abraham Dukas 1865-10-01

Stephen Michael Reich 1936-10-03

Louis Ferdinand Gottschalk 1864-10-07

Toru Takemitsu 1930-10-08

Giulio Romolo Caccini 1551-10-08

Charles-Camille Saint-Saëns 1835-10-09

Einojuhani Rautavaara 1928-10-09

Giuseppe Fortunino Francesco Verdi 1813-10-10

Ralph Vaughan Williams 1872-10-12

Sylvius Leopold Weiss 1687-10-12

Healey Willan 1880-10-12

Ciprian Porumbescu 1853-10-14

Dag Ivar Wirén 1905-10-15

Jan Dismas Zelenka 1679-10-16

Albert Franz Doppler 1821-10-16

Domenico Zipoli 1688-10-17

Herbert Norman Howells 1892-10-17

Baldassare Galuppi 1706-10-18

Howard Leslie Shore 1946-10-18

Charles Edward Ives 1874-10-20

Sir Malcolm Henry Arnold 1921-10-21

Joseph Canteloube 1879-10-21

Franz Liszt 1811-10-22

Ned Rorem 1923-10-23

Johann Baptist (II) Strauss 1825-10-25

Georges Bizet 1838-10-25

Peter Lieberson 1946-10-25

Giuseppe Domenico Scarlatti 1685-10-26

Niccolò Paganini 1782-10-27

Carl Davis 1936-10-28

Peter Warlock 1894-10-30

Unico Wilhelm van Wassenaer 1692-10-30

)

/dps

[Limedrop]

What about something like this...

    import datetime

    now = datetime.datetime.now() 

    the_future = now + datetime.timedelta(days=30)

    query = ((db.employee.dob.month() ==  now.month()) & (db.employee.dob.day() > now.day()))

    query |= ((db.employee.dob.month() ==  the_future.month()) & (db.employee.dob.day() < the_future.day()))

    rows = db(query).select()

A bit rough and not tested, but the point is that you can use datetime.month() as part of the DAL query.

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Jim Steil]

Thanks everyone.  Time for me to call it a night.  I'll give it a run tomorrow morning.

-Jim

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Dave S]

On Tuesday, October 3, 2017 at 5:08:27 PM UTC-7, Dave S wrote:

On Tuesday, October 3, 2017 at 4:48:52 PM UTC-7, Jim S wrote:

I don't think that will do it. The date stored in the database would be 10/15/1980, or 10/21/1993.

Those should both show a birthday in the month of August.

Er, October?
 

But, I don't want just a matching month. I want it for the next 30 days. 

So, on December 15th I should see all birthdays between December 15th and January 15th regardless of what year the birthday is in.

Jim

It's not too much more.  You identify the current day and month, and the day and month of 30 days from now, and do 2 selects:
First for the bdays whose month matches the current and whose day is greater than today.
Second for the bdays whose month matches the successor month and whose day is less than the +30 day.

Here's my actual code.   It doesn't do a thirty day window, just to EOM (and then tacks on a few if being near EOM makes the results short)

def overview():
    results = db((db.composer.birthdate.month()==request.now.month)&(db.composer.birthdate.day()>=request.now.day)).select( db.composer.given_names, db.composer.surname, db.composer.birthdate,
            limitby=(0, 75), orderby=~db.composer.birthdate.month()|db.composer.birthdate.day())
    if len(results) < 3:
        results2 = db(db.composer.birthdate.month()==monthsuccessor(request.now.month)).select( db.composer.given_names, db.composer.surname, db.composer.birthdate,limitby=(0,2), orderby=db.composer.birthdate.day())
        results = results | results2
    return dict(records=results)

def monthsuccessor(month):

    if month == 12:  return 1

    return month + 1

 Right now, the overview results look a lot like the month results below, but start at Gottschalk.

(My example covers years from 1600 to present, for composers.  i also have getting everybody in any single calendar month, which is just one query.  Results for October:

 

(without the table formatting, this looks better in monospaced fonts.  I should have done tabs->spaces, too) 

[Jim Steil]

Thanks, I'll work through this and post my code once I get to the office this morning.

-Jim

--

[Jim S]

Thanks again everyone.

Here is what I came up with for a solution.  I really appreciate all the helpful input.

# find the upcoming birthdays
start_date = request.now.date()
end_date = start_date + relativedelta(months=+1)

this_month = db((db.employee.dob.month() == start_date.month) &
 (db.employee.dob.day() >= start_date.day)).select(db.employee.employeeId)
next_month = db((db.employee.dob.month() == end_date.month) &
 (db.employee.dob.day() <= end_date.day)).select(db.employee.employeeId)
upcoming_birthdays = len(this_month | next_month)

-Jim