469,964 Members | 1,656 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

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

Jun 2 '08 #1
4 2218
On May 22, 1:51 pm, Michael Sharman <sha...@gmail.comwrote:
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.
Jun 2 '08 #2
On May 22, 11:02 pm, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.comwrote:
On May 22, 1:51 pm, Michael Sharman <sha...@gmail.comwrote:
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.
Jun 2 '08 #3
On 22 May, 13:51, Michael Sharman <sha...@gmail.comwrote:
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."
Jun 2 '08 #4
On May 23, 2:33 am, Captain Paralytic <paul_laut...@yahoo.comwrote:
On 22 May, 13:51, Michael Sharman <sha...@gmail.comwrote:
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
Jun 2 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Tiernan | last post: by
3 posts views Thread by Mark Stevens | last post: by
20 posts views Thread by Sims | last post: by
5 posts views Thread by Bob | last post: by
5 posts views Thread by duikboot | last post: by
2 posts views Thread by toedipper | last post: by
6 posts views Thread by Brandon | last post: by
8 posts views Thread by Tony B | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.