How to insert and read time/date in MySQL/PHP?

Question

I want to read the single day, month and year, without adding 3 extra MySQL-rows, in this format (with PHP):

day: 01
month: Jan, Feb, Mar..(first three letters)
year: 2011

This is table and PHP script, which I use now:

I add the date with PHP:

mysql_query("INSERT INTO news (...,`time`) VALUES (...'".date(d.".".m.".".Y)."');");

I read it out with:

$query = "SELECT * FROM news";
$result = mysql_query ($query);

while ($row = mysql_fetch_assoc ($result)) {
echo $row['time'];
}

MySQL table:

news:
time(text):
"27.03.2011"

I'm confused, why are you not just using one of MySQL's built in date/time types, like date? An internal MySQL date would be Y-m-d, not d.m.Y. Furthermore, you're using date() incorrectly - the date codes are in a string, so for this (which I'm not recommending, just correcting your code) it would be date('d.m.Y'). But there's no reason to do that. — Andrew
– Andrew, Commented Aug 5, 2011 at 15:31

Nemanja · Accepted Answer · 2011-08-05 15:33:06Z

1

Query should be:

mysql_query("INSERT INTO news (...,`time`) VALUES (...'".date(d.".".M.".".Y)."');");

M instead of m gives you the 3 letter textual representation of the month.

Get it with:

while ($row = mysql_fetch_assoc ($result)) {
    echo date( 'd', strtotime( str$row['time'] ) );
    echo date( 'M', strtotime( str$row['time'] ) );
    echo date( 'Y', strtotime( str$row['time'] ) );
}

1 Comment

JonasT Over a year ago

thnaks. that was the time-config i was searching for ;)

mkk · Accepted Answer · 2011-08-05 15:31:22Z

1

you can either do it in PHP, check the strftime function or use in SELECT something like

   SELECT DAY(date) as day, MONTH(date) as month, YEAR(date) as year FROM table

and in php you would acccess it as $result['day']. $result['month'] etc. the "date" in SELECT query is of course the name of the column in which you store your date. I would recommend strftime

answered Aug 5, 2011 at 15:31

mkk

7,7037 gold badges48 silver badges63 bronze badges

Comments

John Cartwright · Accepted Answer · 2011-08-05 15:31:47Z

1

You can use MONTH(), DAY(), YEAR() Mysql functions., i.e,

SELECT MONTH(`time`) AS `month`, DAY(`time`) AS `day`, YEAR(`time`) AS `year` FROM `news` [...]

answered Aug 5, 2011 at 15:31

John Cartwright

5,07424 silver badges25 bronze badges

Comments

RiaD · Accepted Answer · 2011-08-05 15:32:50Z

0

SELECT *, substring(1,2) as day, substring(4,2) as month, substring(7) as year FROM table

Also you can(and should) use date table format and use DAY(), MONTH(), YEAR() functions

answered Aug 5, 2011 at 15:32

RiaD

47.8k12 gold badges85 silver badges128 bronze badges

Comments

Michael Berkowski · Accepted Answer · 2011-08-05 15:34:20Z

0

You should be using a DATETIME column in your mysql table. MySQL is then responsible for storing the date in its own internal format, and you can retrieve it in any format you need.

To insert the current date, you can simply

INSERT INTO news (...,`time`) VALUES (...,NOW())

To retrieve it in the format you want, use

SELECT DATE_FORMAT(`time`, '%d.%b.%Y');

Documentation on MySQL DATE_FORMAT()

answered Aug 5, 2011 at 15:34

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Collectives™ on Stack Overflow

How to insert and read time/date in MySQL/PHP?

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related