How to show coming week birthdates? 3

[khwabsheth]

Hello All,

I want to display the employees list whose birth day is coming in next week, in database I am storing birth date in MM/DD/YYYY format.

Anyone can help me with this?

Thank you

Khwab

 

[feherke]

Hi

[blue]mydb=#[/blue] [b]select[/b] * [b]from[/b] employee;
 name  |   birth
-------+------------
 One   | 1950-01-01
 Two   | 1955-01-15
 Three | 1960-02-01
 Four  | 1965-02-10
 Five  | 1970-02-15
 Six   | 1975-02-17
 Seven | 1980-02-20
 Eight | 1985-02-25
 Nine  | 1990-02-28
(9 rows)

[blue]mydb=#[/blue] [b]select[/b] now();
             now
------------------------------
 2007-02-14 16:51:44.05876+02
(1 row)

[blue]mydb=#[/blue] [b]select[/b] * [b]from[/b] employee [b]where[/b] to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'DDD'[/i])::int [b]between[/b] 0 [b]and[/b] 6;
 name  |   birth
-------+------------
 Five  | 1970-02-15
 Six   | 1975-02-17
 Seven | 1980-02-20
(3 rows)

Feherke.

http://rootshell.be/~feherke/

 

[khwabsheth]

Hello Feherke,

First of all thank you very much for helping me with the query, your query works fine but now I want to display all the birthdays in ascending order so What I have done is

select * from employee where to_char(birth,'ddd')::int-to_char(now(),'DDD')::int between 0 and 6 ORDER BY EXTRACT( DOY FROM birth_date);

This works fine but I will get the problem when I access it between 25th dec to 31 dec. Because for first jan "DOY" will be 1 and for 31st dec, it will be 365 and so it will show 1st jan first.

Can you help me with this? I am sure you will be having solutions to this.

Thank you

 

[feherke]

Hi

[b]select[/b] * [b]from[/b] employee,([b]select[/b] to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div) foo [b]where[/b] (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'DDD'[/i])::int+div)%div [b]between[/b] 0 [b]and[/b] 6;

Feherke.

http://rootshell.be/~feherke/

 

[newfriends1981]

Hello Fehereke and All,

select * from employee,(select to_char((to_char(now(),'yyyy')||'-12-31')::date,'ddd')::int as div) foo where (to_char(birth,'ddd')::int-to_char(now(),'DDD')::int+div)%div between 0 and 6;

this quere will just show the result of next week birthdays but it will not show in ascending order order. Also Problem will come when 27th december will come because after 27 th december it is suppose to show result in following format

27 december
28 december
29 december
30 december
31 december
1 january
2 january

but ordinary ascendig query will show result in

1 january
2 january
27 december
28 december
29 december
30 december
31 december

Any suggestions?

 

[feherke]

Hi

[b]select[/b] * [b]from[/b] employee,([b]select[/b] to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div) foo [b]where[/b] (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div [b]between[/b] 0 [b]and[/b] 6 [b]order by[/b] (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div;

[gray]-- or[/gray]

[b]select[/b] * [b]from[/b] ([b]select[/b] *,(to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div [b]as[/b] dif [b]from[/b] employee,([b]select[/b] to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div) foo) bar [b]where[/b] dif [b]between[/b] 0 [b]and[/b] 6 [b]order by[/b] dif;

Feherke.

http://rootshell.be/~feherke/

 

[newfriends1981]

Can you PLEASE explain these queries? Because I am new to postgresql.

Thank you

 

[feherke]

Hi

[b]select[/b]
*
[b]from[/b] (
  [b]select[/b]
  *,
  [gray]-- difference in days between the birth day's and current day's ordinal number[/gray]
  [gray]-- the differences are made continuous, no negative difference[/gray]
  (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div [b]as[/b] dif
  [b]from[/b] employee
  ,(
    [b]select[/b]
    [gray]-- ordinal number of the year's last day = number of days in the year[/gray]
    to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div[/gray]
  ) foo
) bar

[gray]-- where the difference is greater than or equal to 0 and less than or equal 6[/gray]
[b]where[/b] dif [b]between[/b] 0 [b]and[/b] 6

[gray]-- order by the same difference number[/gray]
[b]order by[/b] dif

[gray]/*
to_char(now(),[i]'ddd'[/i]) [i]= ordinal number of current day ( ie, 2007-02-19 is the year's 50[sup]th[/sup] day )[/i]
to_char(now(),[i]'ddd'[/i])::int [i]= the character result of the function casted to numeric value for arithmetic operations[/i]
to_char(now(),[i]'yyyy'[/i]) [i]= the year ( ie, 2007-02-19 is in year 2007 )[/i]
to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date [i]= current year's last day ( ie, 2007's last day is 2007-12-31 )[/i]
to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i]) [i]= ordinal number of the last day of the year( ie, 2007's last day is the year's 365[sup]th[/sup] day )[/i]
*/[/gray]

Feherke.

http://rootshell.be/~feherke/