Insert current date to MYSQL table, then echo back

Question

It seems like there are too many complicated ways of doing this, so I'm looking for a clean, succinct answer to this issue.

I write a blog, I click submit, and the title, content, and timestamp INSERTS INTO my blog table. Later, the blog is displayed on the blogindex.php page with the date formatted as MM-DD-YYYY.

So this is my 3 step question:

1. What is the best column type to insert the date into? (ex: INT, VARCHAR, etc)

2. What is the best INSERT INTO command to use? (ex: NOW(), CURDATE(), etc)

3. When I query the table and retrieve this data in an array, what is the best way to echo it?

I'm new at PHP/MySQL, so forgive me if I don't know the lingo and am too frustrated reading 1000 differing opinions of this topic that do not address my issue specifically, or only cover one of the 3 questions...

Answer 1

I think Styxxy has it pretty well right, but here is a links for your PHP date formatting part... How to format datetime most easily in PHP? (Supporting link: http://php.net/manual/en/datetime.format.php ) Basically it's
echo date("d/m/Y", strtotime('2009-12-09 13:32:15')) ... although, I think the strtotime is unnecessary as it should already have the type of datetime.

In terms of the MySQL, yes, do it as a datetime col, use NOW() as the SQL keyword, and depending on how you want to get it from the database you could...

SELECT CAST(col_name AS DATE) .... or .... SELECT CAST(col_name AS DATETIME) <-- this last one is implied due to the col type.

good luck! :)

Answer 2

Date would probably be the best type, although datetime will work as record more accurate as well.

There isn't a 'best insert into', but what do you really want and how accurate you want the date to be. For a blog, I would say make it datetime and use NOW(). so visitors can see quite accurate of when this post is made.

surely you can easily find huge to run sql and fetch a select query from sql using php by google, so I'll leave this easy work to your self.

For echo the date, you can use the php date format such as:

$today = date("m-d-y");                         // 03-10-01

Answer 3

Always store and process dates and times in UTC and perform timezone adjustments in your presentation layer - it considerably simplifies things in the long-term.

MySQL provides a number of different types for working with dates and times, but the only one you need to worry about is DATETIME (the DATE type does not store time information, which messes up time zone conversion as information is lost, and the TIMESTAMP type performs automatic UTC conversion (which can mess up programs if the system time zone information is changed) and has a smaller range (1970-2038).

The CURDATE() function returns only the current date and excludes time information, however this returns information in the local timezone, which can change. Avoid this. The NOW() function is an improvement, but again, returns data in the current time zone.

Because you'll want to keep everything in UTC you'll actually want to use the UTC_TIMESTAMP function.

To return the value you'll need to execute SQL commands in sequence with variables, like so:

SET @now = UTC_TIMESTAMP()
INSERT INTO myTable ( utcDateTimeCreatedOrSomething ) VALUES ( @now )
SELECT @now

Answer 4

Here is my opinion on your three questions:

1. Use the correct data type: Date or DateTime. I would choose for the DateTime type as you store the time as well (might be very handy if you want to have some kind of order, when you added the posts).

2. It all depends whether you just want the Date (use CURDATE()) or the Date + Time (use NOW()).

3. You fetch the data and format it how you want it. Don't format it yet in the query, just use the correct PHP functions for it (for example with DateTime). How you fetch the data, doesn't matter too much; you can use PDO or MySQLi or ...