UNIX_TIMESTAMP question

Anon

I have a database that stores bithdays in a column. I can extract the date as thus:

$column = "Birthday";

$result = mysql_query ("SELECT UNIX_TIMESTAMP($column) FROM $table_name WHERE RecNum = \"$svRecNum\" ");

$unix_timestamp = mysql_result ($result, 0, 0);

$Birthday_date = date("l, F j, Y", $unix_timestamp);

If you were born on 1970/01/01, this returns the date as:

"Thursday, January 1, 1970"

This works great for MySQL dates that are from 1/1/1970 and later. But how to convert dates to this format for dates earlier than that? Any dates earlier than that are returned as:

"Wednesday, December 31, 1969"

[deleted]

unix time starts on jan 1, 1970, so there's no such unix time stamp before that date.

the reason you're seeing 1969 is probably due to timezone conversion.

So if you want dates before 1970, don't run it through unix_timstamp()

assuming your mysql date field is an actual mysql date field, try something like this instead:

$result = mysql_query ("SELECT Birthday FROM $table_name WHERE RecNum = \"$svRecNum\" ");

$db_bday = mysql_result ($result, 0, 0);
$db_bday = strtotime($db_bday);

$Birthday_date = date("l, F j, Y", $db_bday);

I don't know if strtotime() can handle mysql date fields, so you might have to format the date in the SELECT clause using some other mysql date function found on http://www.mysql.com/doc/D/a/Date_and_time_functions.html

But instinct tells me strtotime can handle mysql's date format so these last two paragraphs are probably pointless. 🙂

Anon

I tried all sorts of formats, but strtotime() just keeps return -1. I don't get it.

For example, even if I try:

$db_bday = "04-01-1951";
$bday = strtotime($db_bday);

$bday still contains a -1.

Also, the docs say that this function returns a UNIX time stamp. Something is definitely fucked up here.

Thanks anyway. I may have to turn to some JScripting. Looks like the PHP library is just piss poor on this one.