Inserting date to database in mysql

Question

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

Re your update - you need to include table name in the select statement - ie users.username — Manse
– Manse, Commented Nov 7, 2011 at 12:25

Manse · Accepted Answer · 2011-11-07 12:18:17Z

3

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

edited Nov 7, 2011 at 12:18

answered Nov 7, 2011 at 12:06

Manse

38.1k11 gold badges88 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

WithFlyingColors Over a year ago

updated my answer..it gives me this: Column 'date' in field list is ambiguous

Manse Over a year ago

@WithFlyingColors you mean question ? you need to include the table name in the select list - ie users.user_name, tablename.date etc etc ...

WithFlyingColors Over a year ago

alright..I got what I wanted..sometimes I get this for some reason

WithFlyingColors Over a year ago

I use this to insert time : $date=time(); and then I insert $date to an insert statemnet

Manse Over a year ago

@WithFlyingColors when you select from multiple tables (ie join) you need to specify the tablename in the select statment

|

Mário Rodrigues · Accepted Answer · 2011-11-07 12:09:17Z

1

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

answered Nov 7, 2011 at 12:09

Mário Rodrigues

8627 silver badges21 bronze badges

4 Comments

Manse Over a year ago

Depends what your column is for - check this out stackoverflow.com/questions/409286/datetime-vs-timestamp

Mário Rodrigues Over a year ago

Never had any kind of problem using TimeStamp type column. Also, is a lot faster that Date column since it uses UNIX Time Stamp like format. So you should use TimeStamp instead of Date. Also, with PHP you get the correct result without a single line of code!

Manse Over a year ago

I didnt say it was wrong to use the TIMESTAMP data type ... just depends on the situation - no idea what the OP is using the column for

Mário Rodrigues Over a year ago

I'm glad to help you! :) Timestamp can also make use of date add and date diff without any issues, at least, for me. Also, since it's a integer value e more accurate and fast to perform queries with, including comparing dates. I've once seen a benchmark. I'll post it here if I see it again :)

Shai Mishali · Accepted Answer · 2011-11-07 12:05:40Z

0

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.

answered Nov 7, 2011 at 12:05

Shai Mishali

9,4425 gold badges60 silver badges83 bronze badges

3 Comments

Manse Over a year ago

Depends what your column is for check this out -> stackoverflow.com/questions/409286/datetime-vs-timestamp

Tadeck Over a year ago

@ManseUK: Good point. There is also some tip on why not to use Unix timestamp within database and why native timestamp is better. It is a good idea not to store Unix timestamps, but use TIMESTAMP column type instead.

Manse Over a year ago

@Tadeck theres are use for both datatypes - certainly agree with that :-)

Distdev · Accepted Answer · 2011-11-07 12:07:48Z

0

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

answered Nov 7, 2011 at 12:07

Distdev

2,32216 silver badges23 bronze badges

Comments

Ian Oxley · Accepted Answer · 2011-11-07 13:10:05Z

0

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:

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

answered Nov 7, 2011 at 13:10

Ian Oxley

11.1k6 gold badges45 silver badges49 bronze badges

Collectives™ on Stack Overflow

Inserting date to database in mysql

5 Answers 5

6 Comments

4 Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

4 Comments

3 Comments

Comments

Comments

Linked

Related