creating mktime data from form data and getting 9- and 10-digit mktime info?

new7media

I am encountering problem using mktime and dealing with dates that fall before year 2000, and dates from 2000 and beyond.

We are collecting info on publications. We ask that people include the date their publication was initially published, and we just ask for the month and year. We have a form that captures the date of a publication like so:

pub_date_month (1-12)
pub_date_year (YYYY)

I then take this data, add in the day date as "1" -- first day of the month -- and convert it with mktime:

pub_date_mktime = mktime(0,0,0,$pub_date_month,1,$pub_date_year);

So far so good. But when I fish all publications out of the dbase with

ORDER BY pub_date_mktime ASC

things don't work correctly. Our earliest year so far is 1993 and the latest year is 2006. But we get a list that looks like this -- I'm abbreviating here:

MO...YEAR....mktime
12...2001...1007182800
12...2002...1038718800
12...2003...1070254800
12...2004...1101877200
12...2005...1133413200
6.....2006...1149134400
1.....1993...725864400
5.....1998...893995200
11...1999...941432400
2.....2000...949381200
12...2000...975646800
9.....2001...999316800

I'm not sure what's up. Why doesn't the list begin with 1993 - the earliest date? Why are we getting, say, a 10-digit mktime for year 2001 in one instance and a 9-digit mktime for year 2001 in another instance? Why are we getting a 9-digit mktime at all?

Should I be using a leading zero on the month data that I'm capturing? I usually use mktime to do date comparisons -- and we do some date comparisons in the site, like "give me all publications that fall between 11/2001 and 2/2002." Of course with this data, we aren't getting what we need.

Any help is GREATLY appreciated! Thanks!

etully

Actually, you are closer to making this work than you think. It's almost right (believe it or not).

First of all, mktime outputs the number of seconds since Dec 31, 1969. So eventually, that number went from a 9 digit number to a 10 digit number. You will notice that your 9 digit 2001 entry was from September and your 10 digit 2001 entry was from December so that tells me that it rolled over to 10 digits sometime in between those dates.

As for the sorting, if you look carefully at the mktime column, it's sorting alphabetically, not numerically. Short answer: Change that field type to int instead of char.

Long answer: This is an old problem in sorting. Which comes first? AAA or BB ? From an alphabetical standpoint, AAA clearly comes before BB. But if you change these to numbers, which comes first, 111 or 99 ? Alphabetically, "1" is less than "9" so 111 is lower than 99. If you change that field type to int, MySQL knows that you want to sort numerically, not alphabetically.

new7media

Hey - thanks so much!! That TOTALLY worked! One other quick question - I switched to integer and I made the length "10". Just making sure that I'm not missing anything, like that in december 2009 I will need room for 11 digits. Really appreciate your help!

etully

Maybe I don't know enough about MySQL - I didn't know that you could specify a length of 10 for an int. I thought you could just select different datatypes like tinyint, smallint, mediumint, int, bigint, float, double, etc.

Numbers are stored as bits in the database, not as characters. The Int datatype uses 4 bytes which is 32 bits. With 32 bits you can store all the integers from:

Negtaive 2147483648 through Positive 2147483647

If you echo date("M Y",2147483647); you will see that with the INT datatype, you are covered through 2038 and it's still only a 10 digit number. 2038 promises to be a fun year.