PHP + MySQL + Dates = ?

Hi guys,

I'm a little confused with dates.

Ok, all I want to do is store a date in MySQL as a datetime object and
be able to read and format it using PHP. My datatype in MySQL is
DATETIME and I'm inserting a date using the MySQL function now(). So
far so good.

When I var_dump() the value from the database it says it's a string,
so I can't use date_format() on it.

What do people mainly do here? I know I can use:

date('Y-M-d H:i:s', strtotime($row["spotdate"]);

But my question is how do I store a datetime object in MySQL so I can
use PHP functions like date_format()?

On May 22, 1:51 pm, Michael Sharman <sha...@gmail.com> wrote:
> Hi guys,
>
> I'm a little confused with dates.
>
> Ok, all I want to do is store a date in MySQL as a datetime object and
> be able to read and format it using PHP. My datatype in MySQL is
> DATETIME and I'm inserting a date using the MySQL function now(). So
> far so good.
>
> When I var_dump() the value from the database it says it's a string,

Yes - PHP's internal time representation is based on Unix timestamps -
there's a function in MySQL to return a Unix timestamp. MySQL formats
dates on output - without the complex mapping implemented by something
like PHP's date(), string is the nearest safe base type.

> so I can't use date_format() on it.
>
> What do people mainly do here? I know I can use:
>
> date('Y-M-d H:i:s', strtotime($row["spotdate"]);
>
> But my question is how do I store a datetime object in MySQL so I can
> use PHP functions like date_format()?

Don't - do your formatting/date calculations in the database - MySQL
will happily handle times from 01 Jan 0 AD (but probably not valid
before the last major calendar changes) to 31 Dev 9999. Unix time only
runs from midnight, 1 Jan 1970 to 03:14:07, January 19, 2038 -less
than a single lifetime.

C.

On May 22, 11:02 pm, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.com> wrote:
> On May 22, 1:51 pm, Michael Sharman <sha...@gmail.com> wrote:
>
> > Hi guys,
>
> > I'm a little confused with dates.
>
> > Ok, all I want to do is store a date in MySQL as a datetime object and
> > be able to read and format it using PHP. My datatype in MySQL is
> > DATETIME and I'm inserting a date using the MySQL function now(). So
> > far so good.
>
> > When I var_dump() the value from the database it says it's a string,
>
> Yes - PHP's internal time representation is based on Unix timestamps -
> there's a function in MySQL to return a Unix timestamp. MySQL formats
> dates on output - without the complex mapping implemented by something
> like PHP's date(), string is the nearest safe base type.
>
> > so I can't use date_format() on it.
>
> > What do people mainly do here? I know I can use:
>
> > date('Y-M-d H:i:s', strtotime($row["spotdate"]);
>
> > But my question is how do I store a datetime object in MySQL so I can
> > use PHP functions like date_format()?
>
> Don't - do your formatting/date calculations in the database - MySQL
> will happily handle times from 01 Jan 0 AD (but probably not valid
> before the last major calendar changes) to 31 Dev 9999. Unix time only
> runs from midnight, 1 Jan 1970 to 03:14:07, January 19, 2038 -less
> than a single lifetime.
>
> C.

Thanks for that, working well now.

In MySQL I'm using:

date_format(MyDate, '%W %D %b')

Now in my PHP I simply have to output $results["MyDate"];

Thanks again.

On 22 May, 13:51, Michael Sharman <sha...@gmail.com> wrote:
> Hi guys,
>
> I'm a little confused with dates.
>
> Ok, all I want to do is store a date in MySQL as a datetime object and
> be able to read and format it using PHP. My datatype in MySQL is
> DATETIME and I'm inserting a date using the MySQL function now(). So
> far so good.
>
> When I var_dump() the value from the database it says it's a string,
> so I can't use date_format() on it.
>
> What do people mainly do here? I know I can use:
>
> date('Y-M-d H:i:s', strtotime($row["spotdate"]);
>
> But my question is how do I store a datetime object in MySQL so I can
> use PHP functions like date_format()?

If all you want is to store a date, you should be using the DATE
datatype, see:
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
where it says:
"The DATE type is used when you need only a date value, without a time
part."

On May 23, 2:33 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 22 May, 13:51, Michael Sharman <sha...@gmail.com> wrote:
>
>
>
> > Hi guys,
>
> > I'm a little confused with dates.
>
> > Ok, all I want to do is store a date in MySQL as a datetime object and
> > be able to read and format it using PHP. My datatype in MySQL is
> > DATETIME and I'm inserting a date using the MySQL function now(). So
> > far so good.
>
> > When I var_dump() the value from the database it says it's a string,
> > so I can't use date_format() on it.
>
> > What do people mainly do here? I know I can use:
>
> > date('Y-M-d H:i:s', strtotime($row["spotdate"]);
>
> > But my question is how do I store a datetime object in MySQL so I can
> > use PHP functions like date_format()?
>
> If all you want is to store a date, you should be using the DATE
> datatype, see:http://dev.mysql.com/doc/refman/5.0/en/datetime.html
> where it says:
> "The DATE type is used when you need only a date value, without a time
> part."

Sorry, I wasn't clear in my original post. I actually want a datetime,
was just having problems with the formating as PHP treats dates as
timestamps rather than odbc datetime objects etc