Converting date (value) into a timestamp

[havoc]

I am wokring with time and dates at the moment but i have having a bit of trouble , well not knowing how to convert an input put in by a user and converting it to a time stamp

lets say i have a DOB : 01 / 02 / 90
So Day = 01 month = 02 year = 1990

how would i got about changing this into a unix timestamp Or mysql timestamp?

i can go the other way lol :)

[httpwebwitch]

mktime() or strtotime() both work well.

These functions use 32-bit integers, which means they often can't represent dates from before 1970 (which produce a negative timestamp). It's OK if you're always storing the current time, but not OK if you're describing historic dates like birthdays or battles.

see this recent thread [webmasterworld.com] for my ranting on this subject and a good solution.

If I recall correctly, a mySQL timestamp has a format like "YYYYMMDDhhmmss" or something like it. Try to use the built-in mySQL functions that handle dates and times - they are tricky, but useful once you learn them.

[havoc]

thanks i will read up on it :)

[havoc]

yup i just found out about using stamps . Time to read up on mysql stamps :)

[havoc]

Ok i am using 0000-00-00 DATE mysql function
How would you convert it back to a format that you can work with in php.

SO mysql field "DOB" = "1965-12-23"
how would i break that up into php $year $ month $day

i tried using SELECT DATE_FORMAT('dob', '%W %M %Y') AS 'dob'FROM `users` WHERE user = '$id';

I am probably doing it all wrong , i am pretty novice at mysql functions. Time is the only thing i cant work out always had trouble with it.

[coopster]

No, you have it (almost) right. You need to remove the quotation marks from the column name as MySQL thinks you are passing it a string date rather than referencing a column in your table:

SELECT DATE_FORMAT(dob, '%W %M %Y') AS dob FROM users WHERE user = '$id';

[havoc]

arhhhhhh! :) i know it would be some thing stupid!
is there anyway i can pit it out as $year $month $day though? or will i have to do 3 queries?

[coopster]

Just modify the DATE_FORMAT [dev.mysql.com].

DATE_FORMAT(dob, '%Y %m %d')

[havoc]

Basically what this is for is this weeks bday fucntion

So if someone is born 1975-09-09, In the WEEK of thier bday (sunday to sunday) i want mysql to pull it out of the dbase for the week saying this weeks bdays are: jo and blo :)

oh lets say the date is 2004-09-07
i would say ok ... (i hate time heh)

in english "Mysql i want records that are within the same week as 09-07" Now i have been playing with this for a week
i think im over thinking it . What procedure should i take should i get the result i require. i mean i don't nessaccerily need the code just the way i should do the processing :)

Thanks to everyone thats been helping

[havoc]

ok we came up with this

SELECT * FROM table WHERE week(YEAR(now()) & '-' & MONTH(dob) & '-' & DAY(dob)) = WEEK(LOCALTIME())
SELECT * FROM table WHERE week(MAKEDATE(YEAR(now()),DAYOFYEAR(dob))) = WEEK(now())

only thing is makedate doesn't exist in mysql version on the server i'm on ... any suggestions?

what is the right operator to join stuff together
SELECT YEAR(now()) & '-' & MONTH(dob) & '-' & DAY(dob) FROM users & doesn't work

[coopster]

CONCAT [dev.mysql.com], or better yet, CONCAT_WS [dev.mysql.com]:

SELECT 
 * 
 FROM table 
 WHERE WEEK(CONCAT_WS('-',YEAR(NOW()),MONTH(dob),DAY(dob))) = WEEK(NOW()) 
;

[havoc]

Still not working ... argh
Will keep trying

[havoc]

Ahh my version of mysql was suckie this worked though

SELECT * FROM users WHERE WEEK(CONCAT_WS('-',YEAR(NOW()),MONTH(dob),DAYOFMONTH(dob))) = WEEK(NOW())

Day() wasnt supported