[CLUE-Tech] PHP or MySQL date handling problem

[Jed S. Baer]

On Fri, 14 Jun 2002 12:00:22 -0600
Lynn Danielson <lynnd at techangle.com> wrote:

> > > Jed S. Baer wrote:
> > > > I've run into a problem with PHP or MySQL (or both) with the
> > > > handling of dates. The problem is that neither of them provides a
> > > > generalized date/time parser that accepts a format string. <snip>
...
> > Thanks Lynn, but nope. I have the string. I need to convert it to
> > something that works for MySQL. No PHP function accepts a date/time 
> > string and a format string. (Look at man strptime. ...)
> 
> Ok.  I did look at strptime and I'm not getting your problem.  If I'm
> understanding correctly, you have a date string and want to convert it
> into a MySQL compatible datetime or timestamp input format.

Yep, that's it. Let's say I have this nice proggy which will (eventually)
have a preferences screen where the user (me) gets to enter a preferred
date format. Using the MySQL format codes, the string is '%Y-%b-%d'.

mysql> select date_format('2002-03-23','%Y-%b-%d');
+--------------------------------------+
| date_format('2002-03-23','%Y-%b-%d') |
+--------------------------------------+
| 2002-Mar-23                          |
+--------------------------------------+

> If it's the tm structure you're looking for, an equivalent hash could
> be constructed using PHP's date("format", strtotime("date/time string"))
> functions.  I can't see what strptime gives me that a combination of
> PHP's date() and strtotime() do not.  Please enlighten me.

strtotime() will take lots of dates as input:

  http://www.gnu.org/manual/tar-1.12/html_chapter/tar_7.html

(from the strtotime PHP manual page). Note the above format isn't there.
Nonetheless, I fed it in there, with interesting results:

  echo $$column_name, '<br>';
  echo 'Time strtotime ', strtotime($$column_name), '<br>';
  echo 'strtotime = ', date('Y-m-d',strtotime($$column_name)), '<p>';

Produces:

  2002-mar-23
  Time strtotime 1850536800
  strtotime = 2028-08-22

Also, reading the strtotime man page, it's supposed to return -1 on
failure, but it appears it doesn't do that, for example, when I feed it
something absurd such as '1200-08-99'. I need to experiment with this a
bit more though. As an aside, I was going to use strtotime as my date
validator, since MySQL just converts any invalid date to zero, and
proceeds with the operation, instead of signaling an error (bad MySQL!).

strptime takes as input a date string, and a format string. And,
fortunately, the MySQL folks use the same format specifiers, so if it were
available in either PHP or MySQL, I could simply use the same
user-supplied format string from the preferences to convert both ways. The
equivalent in PHP would be if strtotime were:

  $utimestamp = strtotime($timestring,$formatstring);

Or in MySQL:

  insert into foo set date_field = to_date('2002-Mar-23','%Y-%b-%d);

to borrow from Oracle's function list.

This is starting to become an academic discussion, since it looks like
I'll have to sacrifice (my definition of) user-friendly for what I
consider to be a glaring hole in the language. If I'm going to allow a
user-specified format string, then it appears I've lost my train of
thought.

Thanks for your input, though. I'd be happy to hear any other ideas.

jed
-- 
We're frogs who are getting boiled in a pot full of single-character
morphemes, and we don't notice. - Larry Wall; Perl6, Apocalypse 5