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, Nov 07 '11 at 12:25

Manse · Accepted Answer · 2011-11-07T12:18:17.893

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 07 '11 at 12:18

answered Nov 07 '11 at 12:06

Manse

37,765
10
83
108

updated my answer..it gives me this: Column 'date' in field list is ambiguous – WithFlyingColors Nov 07 '11 at 12:25
@WithFlyingColors you mean question ? you need to include the table name in the select list - ie `users.user_name, tablename.date` etc etc ... – Manse Nov 07 '11 at 12:26
alright..I got what I wanted..sometimes I get this for some reason – WithFlyingColors Nov 07 '11 at 12:28
I use this to insert time : $date=time(); and then I insert $date to an insert statemnet – WithFlyingColors Nov 07 '11 at 12:30
@WithFlyingColors when you select from multiple tables (ie join) you need to specify the tablename in the select statment – Manse Nov 07 '11 at 12:30
@WithFlyingColors you could use the sql function NOW() -> http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now – Manse Nov 07 '11 at 12:30

score 1 · Answer 2 · answered Nov 07 '11 at 12:09

1

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

answered Nov 07 '11 at 12:09

Mário Rodrigues

833
7
21

Depends what your column is for - check this out http://stackoverflow.com/questions/409286/datetime-vs-timestamp – Manse Nov 07 '11 at 12:10
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! – Mário Rodrigues Nov 07 '11 at 12:21
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 – Manse Nov 07 '11 at 12:23
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 :) – Mário Rodrigues Nov 08 '11 at 11:48

score 0 · Answer 3 · answered Nov 07 '11 at 12:05

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 07 '11 at 12:05

Shai Mishali

9,224
4
56
83

1

Depends what your column is for check this out -> http://stackoverflow.com/questions/409286/datetime-vs-timestamp – Manse Nov 07 '11 at 12:11
@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. – Tadeck Nov 07 '11 at 12:21
@Tadeck theres are use for both datatypes - certainly agree with that :-) – Manse Nov 07 '11 at 12:23

score 0 · Answer 4 · answered Nov 07 '11 at 12:07

0

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

answered Nov 07 '11 at 12:07

Distdev

2,312
16
23

score 0 · Answer 5 · answered Nov 07 '11 at 13:10

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

Inserting date to database in mysql

5 Answers5