OT: MySQL Dates Questions

i've been storing dates in mysql database tables in fields formatted
date (e.g. 2008-08-02). want learn how manipulate data
(e.g. displaying years or months or sorting days).

i found several references confusing , got mixed
advice on forum.

first, plan on using php manipulate , store dates. given
the following code display dates in text format (e.g. may 8, 2008):

date("f d, y",strtotime($date))

however, doesn't appear work dates earlier 1900.

also, told me should change date fields timestamp.
however, haven't yet figured out how format data timestamp
or assign character length timestamp. i'm getting sorts of
weird results. in meantime, mysql quru told me forget
timestamp.

so, i'd ask methods use manipulate , display
dates. dates consist of birth dates , dates various
people elected public office. able display
them in various formats (e.g. 2008-04-08, 2008, april 8, 2008).
eventually, i'd able sort table columns such
variabls year, month or date.

it might nice able calculate things like...

may 7, 1777 = ? years ago
1777 , 1848 = difference of how many years?

can sorts of things php if dates stored in date
fields rather timestamp? if have use timestamp, how set
it up? read doesn't accept dates "include" number 0,
but doesn't make sense. assume means timestamp dates can't
begin 0, changed 1884-04-12 1884-4-12. nixed
zeroes - displays current date.

i'm working phpmyadmin, i'm not can handle timestamp.

thanks tips.

.oo(david blomstrom)

>i've been storing dates in mysql database tables in fields formatted
>date (e.g. 2008-08-02). want learn how manipulate data
>(e.g. displaying years or months or sorting days).

mysql knows functions day(), month(), year() , date_format().
the order clause should obvious.

>i found several references confusing , got mixed
>advice on forum.
>
>first, plan on using php manipulate , store dates. given
>the following code display dates in text format (e.g. may 8, 2008):
>
>date("f d, y",strtotime($date))
>
>however, doesn't appear work dates earlier 1900.

correct. unix timestamps signed 32 bit integers. negative
timestamps can around 1901-2038.

you should let db formatting date_format(). see
manual details , format parameters.

>also, told me should change date fields timestamp.

timestamp fields needed if want let db update them
automatically on record's update. other things date or
datetime types enough.

>so, i'd ask methods use manipulate , display
>dates. dates consist of birth dates , dates various
>people elected public office. able display
>them in various formats (e.g. 2008-04-08, 2008, april 8, 2008).
>eventually, i'd able sort table columns such
>variabls year, month or date.

use date or datetime column types, date_format()
formatting , order sorting. it's simple.

>it might nice able calculate things like...
>
>may 7, 1777 = ? years ago
>1777 , 1848 = difference of how many years?

date_add()/date_sub()/datediff()/...

there lot more useful date , time functions. check out
manual.

11.6. date , time functions
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

>can sorts of things php if dates stored in date
>fields rather timestamp?

whenever db can - let db it. it's more efficient
than in php.

micha

More discussions in Dreamweaver support forum

adobe