Re: date problem
| From: | Miguel Cruz | Date: | Thu, 06 Jun 2002 06:04:02 +0000 |
| Subject: | Re: date problem | ||
| References: | 1 | Groups: | php.db php.general |
| Request: | Send a blank email to php-db+get-19636@lists.php.net to get a copy of this message | ||
On Thu, 6 Jun 2002, andy wrote:
> I would like to count the users out of a mysql db who registered after a
> certain date.
>
> The column I have in the db is a char and I do not want to change this
> anymore.
> This is how a typical entry looks like: May 29, 2002
>
> This is how I tryed it:
>
> // while '10...' is unix timestamp june 1, 02
> SELECT COUNT(*) AS c
> FROM users_table
> WHERE UNIX_TIMESTAMP( user_regdate ) > '1022882400'
You can only call UNIX_TIMESTAMP on a DATE or DATETIME field, not on just
any generic CHAR/VARCHAR/TEXT/whatever. "May 29, 2002" isn't a MySQL
timestamp, so I'm guessing you have a textual field type.
The lesson of all this is: Convert dates to either unix or database-native
date format before storing them in the database. Things like "May 29,
2002" are useless in a database.
At this point I'd recommend running a quick script to strtotime() all your
dates and then re-write them to a new field that's in a proper format.
miguel