FROM_UNIXTIME Problem - MySQL 5.0.77

[iInstallUnattended]

Hi Everyone, I'm new to this forum with hopes you could provide me some assistance on a problem I'm having.

 

I built a MySQL database forever ago that stores timestamps as UNIX stamps using mktime()

 

PHP is able to read every portion of this timestamp in a million different ways, once it's queried out of MySQL.

 

I've got a script that is meant to query the birthdays from an Employee database. It does it on a month-to-month basis. Therefore, I need to query all of the Birthdays that happen in a certain month, then pull their name and day of the month appropriately. On my old server, which had MySQL 4(something) it worked just fine using this:

 

SELECT * FROM employees WHERE MONTH(FROM_UNIXTIME(birthday)) = 9

 

In short, it would pull people with a birthday in September, with the code above (as if many of you didn't know that).

 

The problem is, with MySQL 5.0.77 whenever I run the exact same query, it only returns if the field contains a UNIX timestamp that is AFTER the epoch. It seems to not want to read, or whatever the case may be, any UNIX timestamps that are negative. I've got it saved as a text field, but an Integer field didn't help me when I tried changing it.

 

I've also tried:

 

SELECT * FROM employees WHERE FROM_UNIXTIME(birthday, '%m') = 9

 

It does the exact same thing as the code snippet above.

 

I've been searching for an answer to this for a while now and have had no success. I'd rather not change the field type since there's a lot of scripting that front-ends this format. It kills me because everything I'm doing worked fine in MySQL 4....

 

Thanks in advance.

[PFMaBiSmAd]

I would try it for both a signed and an unsigned integer and re-import your data as it was probably altered if it was imported into a type that could not hold the intended value (have you verified that the exported data from the old database is actually correct?)

[JAY6390]

Can you give some examples of the data for the field birthday?

[iInstallUnattended]

id   birthday
1	-306270000
2	238482000
3	-332708400
4	-574282800
5	-567630000
6	-488574000
7	-401050800
8	-786052800
9	336286800
10	454395600
11	-642366000
12	-176842800
13	-92257200
14	419576400
15	-575838000
16	131173200
17	-176583600
18	375080400
19	310798800
20	-1098558000

 

Without showing sensitive information, that's example of the database. They are saved as Text fields and each represents a UNIX timestamp created by mktime(). As you can see, some are saved as negative and some are saved as positive. All represent seconds before or after the UNIX Epoch.

 

I noticed that everything I'm doing worked perfectly fine in PHP4 / MySQL 3. Our old host once updated us to MySQL 5 and left PHP on 4, and I noticed that searching for anything using the MySQL FROM_UNIXTIME function in the query only returned results that are AFTER the Epoch and not before. At that point in time, I asked them to revert our MySQL server back to 3 since we gained nothing from having the update. It was easier than fixing it, at the time. This situation proves that the data itself has nothing to do with it. It's definitely something related to the version of MySQL (at least 5.0.77).

 

What I would have to wonder is if this is a bug in the version that gets corrected? Will updating to any version 5 or better cause this problem? Is there something I should be doing differently to counteract a depreciated function? I haven't a clue. I've had no real success in finding an answer either.

 

While the signed and unsigned integer might help, I can say that simply changing the field to Integer didn't. I tried that before posting here.

 

Ideally, if I can at least understand what is wrong with this method, I can come up with a decent fix. At this point, I'm doing what I think should work.... and it just doesn't...

 

Anyway, appreciate any and all input!

 

Edit: Wrong MySQL version mentioned.

[PFMaBiSmAd]

The mysql documentation only states that a Unix Timestamp is valid starting from '1970-01-01 00:00:00'. You might consider yourself lucky that it worked at all.

 

Negative Unix Timestamps in php only work under later php versions and is also dependent on operating system.

 

I would seriously consider adding a column with a DATE data type and populate it with the actual yyyy-mm-dd values from your Timestamp values and then use that.

 

They are saved as Text fields

What exact type, because some types have trailing blank issues that could possibly affect how they are treated as data.

 

One test you could do under the mysql5 system would be to insert a pre 1970 value and see what is inserted and see what you get when you retrieve it.

 

Edit: I briefly searched the mysql 5.0 change log and did not find any mention of anything associated with a Unix Timestamp.

[JAY6390]

I'd have to agreee with PFM. Your best bet would be to use a script to add the dates to a new column called bday or something like that, keeping the original birthday column as you have it already, and putting proper date formats in those date fields. For example use the following script. It will add the field `bday` to your table `employees` and update each of the birthdays

<?php

/**
* @author Jay Gilford
*/

mysql_connect('localhost','root',''); //Change these to your database login details
mysql_select_db('test'); //Change this to your database name

$query = "ALTER TABLE `employees` ADD `bday` date";
mysql_query($query);

$query = "SELECT `id`, `birthday` FROM `employees`";
$res = mysql_query($query);

while($row = mysql_fetch_assoc($res)) {
$query = sprintf("UPDATE `employees` SET `bday` = '%s' WHERE `id` = '%s'",
date('Y-m-d', $row['birthday']),
$row['id']);
mysql_query($query);
}

[iInstallUnattended]

I also did a search through the change logs. Didn't see anything either. At the same time, I did read more about this error about a year or two ago when it happened and I simply reverted the old server's mysql version.

 

My last resort was to change to the data field type, the only reason I didn't do this right away was due to the amount of script rewrites this change would require. "Ah well, guess ya gotta' do what you gotta' do".

 

Is there any downside to the DATE field type? Any sort of instances where it won't work properly?

 

I currently format my query returns using

 

date($row['field'], 'm-d-Y');

 

Will I need to change the way I handle this information? Thanks!

[PFMaBiSmAd]

I recommend using the mysql DATE_FORMAT() function directly in your queries to format the DATE yyyy-mm-dd into any format you want - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

 

If you want to do this in php, you would add the the strtotime function to your date() code you have now -

 

$var = date('m-d-Y', strtotime($row['field']));

[iInstallUnattended]

Great information.

 

Thanks.

[PFMaBiSmAd]

Is there any downside to the DATE field type? Any sort of instances where it won't work properly?

 

No there is no downside. The value you put into a DATE will always be that value. It is not affected by any differences in interpretation, conversion errors, DST database changes, or timezone settings. And in fact since you can use the couple of dozen mysql date/time functions on a DATE, your code can be quicker (conversion into and out of a Unix Timestamp is fairly slow) and shorter. You can directly query for dates in the current month for example or directly do date math or interval checks without needing to use any slow parsed/tokenized/interpreted php code.

 

And the DATE type is not subject to the 1970(or 1901)/2038 limitations that make a Unix Timestamp hard impossible to use for some things.