CODEWITHSUNDEEP
phpmysql
WithFlyingColors
WithFlyingColors

Reputation: 2770

Inserting date to database in mysql

I use NOW() function but I get this weird date:

2011-11-06

How do I get the following European date:

ss:mm:hh dd:mm:year (06-11-2011)

In my database I set the field column date as DATE

How do you integrate DATEFORMAT into this query:

   $avatar_Q=mysql_query("
                SELECT user_name,avatar,reputation,comment, DATE_FORMAT(date,'%d/%m/%Y %h:%i') AS rightNow
                FROM comments AS com
                INNER JOIN users AS us ON com.user_id=us.user_id
                WHERE comment_id=$commentID
       ") or die(mysql_error());

The date is is in the column table

Upvotes: 0

Views: 226

Answers (5)

Ian Oxley
Ian Oxley

Reputation: 11056

That format is just how MySQL stores the data type DATE: http://dev.mysql.com/doc/refman/5.5/en/datetime.html.

If you're using the DATE data type for you column you can either:

  1. Use the DATE_FORMAT function in your SQL to get the date in your desired format
  2. Use a combination of PHP's strtotime and date functions to display your date in the most appropriate format

Upvotes: 0

Manse
Manse

Reputation: 38147

MySQL uses the following date format - YYYY-MM-DD - if you want a different format you need to use the DATE_FORMAT function on select

for example :

SELECT DATE_FORMAT(datecolumn,"%d/%m/%Y %h:%i")
FROM atable

To integrate the date_format function into your select statement you need to list the fields individually

Upvotes: 3

Mário Rodrigues
Mário Rodrigues

Reputation: 862

Yep, use a Timestamp column and insert with CURRENT_TIMESTAMP. It saves a lot of time! :)

Upvotes: 1

Distdev
Distdev

Reputation: 2312

DATE_FORMAT http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Upvotes: 0

Shai Mishali
Shai Mishali

Reputation: 9402

My personal recommendation is to save the date as a UNIX Timestamp (using the time() function) ,

This way you could format it as you wish .

Shai.

Upvotes: 0

Related Questions