PHP + MySQL + Dates = ?

[Michael Sharman]

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()?

[C. (http://symcbean.blogspot.com/)]

On May 22, 1:51 pm, Michael Sharman <sha...@gmail.c omwrote:

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.

[Michael Sharman]

On May 22, 11:02 pm, "C. (http://symcbean.blogsp ot.com/)"
<colin.mckin... @gmail.comwrote :

On May 22, 1:51 pm, Michael Sharman <sha...@gmail.c omwrote:

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(MyD ate, '%W %D %b')

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

Thanks again.

[Captain Paralytic]

On 22 May, 13:51, Michael Sharman <sha...@gmail.c omwrote:

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."

[Michael Sharman]

On May 23, 2:33 am, Captain Paralytic <paul_laut...@y ahoo.comwrote:

On 22 May, 13:51, Michael Sharman <sha...@gmail.c omwrote:

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