[thelist] MySQL date format

[Anthony Ettinger]

if it's a timestamp, i would use TIMESTAMP data type. (ie - when a user is
created).

Then you can format it later however you want with php, as stated by Symeon.

On 11/28/05, Symeon Charalabides <symeon at systasis.com> wrote:
>
> > Anyway, AFAIK
> > you'll need to select three dates and CONCAT them for your desired
> > format. Assuming thedate is a datetime column type, it might be
> > something like this:
> >
> > SELECT CONCAT(
> >    DATE_FORMAT(mydate, "%W, %e - "),
> >    DATE_FORMAT(DATE_ADD(mydate, INTERVAL 1 DAY), "%e"),
> >    DATE_FORMAT(mydate, " %M %Y")
> > ) as thedate
> > Example output:
> > Saturday, 26 - 27 November 2005
> > Tuesday, 26 - 27 July 2005
> > Friday, 3 - 4 June 2005
>
> Thanks, this is an excellent example.
> >
> > Of course this format fails to take into account the last day of the
> > month or year (giving you e.g. Saturday, 31 - 1 December 2005), so you
> > would probably need to improve this.  All in all, it's usually better to
> > use PHP (or whatever) to format your dates for ouput.
>
> True, this is what I ended up doing. I was wondering if it was possible to
> bring
> up such a string (or some variation thereof) with a simple SELECT, as it's
> faster
> than digging up the conventional date, then formatting it with PHP. As it
> turns
> out this isn't possible as the SELECT needed is so complex that PHP
> probably does
> it faster. So I may as well just write
>
> date('l j - ', strtotime($row['day'])).
> date('l j ', strtotime($row['day'])+60*60*24).
> date('F Y', strtotime($row['day']))
>
> like I used to. So much for learning new tricks...
>
> Symeon Charalabides (cosmopolite trainee)
> -------------------------------------------------
> http://www.systasis.com
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



--
Anthony Ettinger
hCard: http://chovy.dyndns.org/hcard.html