Pull Unix Timestamp as M/D/Y

[blasterstudios]

If i store my dates as Unix timestamps, how can i pull the records and separate the month, day, and year values. Basically I need their name, age, and month/day of the birthday. I also need 1 filter in there that checks if the birthday has been approved by an admin yet (approved=1). My field names are name, dob, and approved.

I found some script that may help but there are some parts that I don't know what's going on to know what to change:

SELECT name, approved, DATE_FORMAT(dob, '%M %e, %Y') AS birthday, MONTHNAME(birthday) AS month, DAYOFMONTH(birthday) AS day, YEAR(birthday) AS year, (YEAR(NOW()) - YEAR(birthday) + (RIGHT(CURRENT_DATE,5)>RIGHT(birthday,5))) AS age, (TO_DAYS((birthday + INTERVAL (YEAR(CURRENT_DATE)-YEAR(birthday) + (RIGHT(CURRENT_DATE,5)>RIGHT(birthday,5))) YEAR)) - TO_DAYS(CURRENT_DATE)) as daysAway FROM df_birthdays WHERE birthday != '' AND (TO_DAYS((birthday + INTERVAL (YEAR(CURRENT_DATE)-YEAR(birthday) + (RIGHT(CURRENT_DATE,5)>RIGHT(birthday,5)) ) YEAR)) - TO_DAYS(CURRENT_DATE)) < $option->bdayInterval AND approved = 1 ORDER BY daysAway ASC, age DESC

the first part that messes up is the $option-> part. I do not have a variable called $option. I guess the script where I found this did. If that is for how long I want the days set to, i'd like for it to be 45 days. Anybody understand this enough to know how to change it?
I also want to sort ascending starting with today (which is a variable - $today). This is basically to get upcoming birthdays. Thanks for any help.

 

[blasterstudios]

ok, i was able to change out all the problems with this one to fit my needs but its still not working. It doesn't pull the age correctly and it misses the negative ones (the ones before 1970). It also isn't getting birthdays only 45 days away. What is wrong with it?

SELECT
   name,
   FROM_UNIXTIME(dob,'%c/%e') AS bday,
   FROM_UNIXTIME(dob,'%c') AS month,
   FROM_UNIXTIME(dob,'%e') AS day,
   FROM_UNIXTIME(dob,'%Y') AS year,
   (YEAR(NOW()) - FROM_UNIXTIME(dob,'%Y') + (RIGHT(CURRENT_DATE,5) > FROM_UNIXTIME(dob,'%Y'))) AS age,
   (TO_DAYS((FROM_UNIXTIME(dob,'%Y-%c-%e') + INTERVAL (YEAR(CURRENT_DATE)-FROM_UNIXTIME(dob,'%Y') + (RIGHT(CURRENT_DATE,5)>FROM_UNIXTIME(dob,'%Y'))) YEAR)) - TO_DAYS(CURRENT_DATE)) as daysAway,
   approved
FROM
   df_birthdays
WHERE
   (TO_DAYS((FROM_UNIXTIME(dob,'%Y-%c-%e') + INTERVAL (YEAR(CURRENT_DATE) - FROM_UNIXTIME(dob,'%Y') + (RIGHT(CURRENT_DATE,5) > FROM_UNIXTIME(dob,'%Y')) ) YEAR)) - TO_DAYS(CURRENT_DATE)) < 45 AND
   approved = 1
ORDER BY
   daysAway ASC,
   age DESC

 

[KarveR]

Its a good advert for using a plain date in there somewhere.

Need to let the morning cuppa fester into my brian, will call back later.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[nego78]

KarveR is right - you must insert column with date type
Unixtime is limited from 1970

Via-Net - web directory

 

[TonyGroves]

If you're using MySQL 4.1, you could try:
[tt]
SELECT
name,
DATE_FORMAT(realdob,'%c/%e') bday,
MONTH(realdob) month,
DAYOFMONTH(realdob) day,
YEAR(realdob) year,
YEAR(CURDATE())
-YEAR(realdob)
-(RIGHT(realdob,5)>RIGHT(CURDATE(),5))
age,
DATEDIFF(
CONCAT(
YEAR(CURDATE())
+(RIGHT(realdob,5)<RIGHT(CURDATE(),5)),
RIGHT(realdob,6)
),
CURDATE()
)
daysaway
FROM
(
SELECT name,DATE('1970-01-01 12:00:00' + INTERVAL dob SECOND) realdob
FROM df_birthdays
WHERE approved=1
)
sq
[/tt]
However, as previously mentioned, if you can store your dates in MySQL format, it would be much better.

 

[TonyGroves]

Sorry, I forgot the 45 days bit. You could just wrap the query as follows:
[tt]
SELECT *
FROM
(
[the original query]
)
sq2
WHERE daysaway<45
ORDER BY daysaway,age DESC
[/tt]

 

[blasterstudios]

I'm not too familiar with MySQL date format. What format do i need to put it into the database where I'll be able to pull everything (m/d/y)?

 

[TonyGroves]

To use MySQL date format, declare your date fields as DATE. To store values in those fields, use the form 'yyyy-mm-dd' (or 'yyyymmdd' or yyyymmdd). When you retrieve DATE values, they will be in the form 'yyyy-mm-dd'.

 

[blasterstudios]

ok, can you explain quickly what you mean by "declare your date fields as DATE"?

Does that mean when inserting them into the database, use DATE($myvar)?

 

[TonyGroves]

When you're creating your tables, use the DATE field type:
[tt]
CREATE TABLE people
(
name CHAR(20),
dob DATE
)
[/tt]
When you're inserting values, use the form 'yy-mm-dd':
[tt]
INSERT tbl
VALUES
('Tom','1976-10-22'),
('Mary','1982-04-01')
[/tt]

 

[TonyGroves]

Sorry, I meant use the form 'yyyy-mm-dd'.