PHP to MySQL date formatting issue

Question

I have a PHP/MySQL date formatting problem. Here, i create the date variables:

$checkDate = date("d/m/y H:i", time());
$datestrto = strtotime($checkDate);

Then i insert it to a mysql table with the column Datatype of bigint.

When i then, later on, when i need to echo the date, i use this code:

echo '<td>'.date("d/m/y H:i",$row['f_uploaded_date']).'</td>';

But istead of echo'ing the date in the format D/M/Y H:i, it echo'es the date in the format of m/d/y H:i. Can anyone explain why this is happening, and how to fix it?

Thank you in advance, Adam

Why the transformation in the first place and not inserting $checkDate directly? — Sirko
– Sirko, Commented Mar 8, 2012 at 11:08
Datatype you are saving is bigint i think that is the problem — Naveen Kumar
– Naveen Kumar, Commented Mar 8, 2012 at 12:48

FeRtoll · Accepted Answer · 2012-03-08 14:39:28Z

1

When you insert it into the MySQL table do it like this:

INSERT INTO yourtable(something,somedate) VALUES('something',str_to_date('".$checkDate."','%d/%m/%y %H:%i'))

and when you pull it out from MySQL then do it like this:

SELECT *,date_format(somedate,'%D/%M/%Y %H:%i') as formateddate from yourtable

then in php you use:

$row['formateddate']

Hope it helps you :)

EDIT: The complete code:

$ddate = date("d/m/y H:i", time()); 
$sql = "INSERT INTO files (rowID, file, mimetype, data, uploaded_by, uploaded_date, size, times_downloaded, description) VALUES (NULL, '$fileName', '$fileType', '$content', '$user', str_to_date('".$ddate."','%d/%m/%y %H:%i'), $fileSize, 0, '$description')";

edited Mar 8, 2012 at 14:39

answered Mar 8, 2012 at 11:11

FeRtoll

1,26711 silver badges27 bronze badges

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

4 Comments

Theadamlt Over a year ago

Hmm.. So like this?:

$date = date("d/m/y H:i", time()); $sql = "INSERT INTO files (rowID, file, mimetype, data, uploaded_by, uploaded_date, size, times_downloaded, description)  	VALUES (NULL, '$fileName', '$fileType', '$content', '$user', str_to_date('".$checkDate."','%d/%m/%y %H:%i'), $fileSize, 0, '$description')";

FeRtoll Over a year ago

yes but you changed variable name to $date and in your insert it is $checkDate so be sure to check that :)

FeRtoll Over a year ago

point is that "Theadamlt" better understand whats going on here and that he solves his problem.

Theadamlt Over a year ago

Hi @FeRtoll, thank you for the help! col-shrapnel, i'm doing it this way, so i, later on, can compare dates

symcbean · Accepted Answer · 2012-03-08 11:11:35Z

1

So you convert a unix timestamp into a formatted date string then convert the formatted time string back into a unix timstamp and insert that into your database. Wouldn't it just be simpler to just insert the unix timestamp you got in the first place? Or not even bother with PHP code and

INSERT INTO sometable (id, f_uploaded_date) 
VALUES ($id, UNIX_TIMESTAMP(NOW()))

?

I suspect the explicit problem you describe is due to the fact that strtotime expects date strings of format 99/99/9999 to be the american style of mm/dd/yyyy rather than dd/mm/yyyy as used in the UK.

answered Mar 8, 2012 at 11:11

symcbean

48.4k6 gold badges64 silver badges99 bronze badges

Comments

Your Common Sense · Accepted Answer · 2012-03-08 11:34:30Z

1

First,

$checkDate = date("d/m/y H:i", time());
$datestrto = strtotime($checkDate);

is quite funny way of assigning time() frunction result to $datestrto variable.

Next, you don't need that variable either, as you just can use unix_timestamp() mysql function in the insert query.

Now to your question.

istead of echo'ing the date in the format D/M/Y H:i, it echo'es the date in the format of m/d/y H:i.

double-check your syntax. there is a typo somewhere.

answered Mar 8, 2012 at 11:34

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

2 Comments

Naveen Kumar Over a year ago

He is saving date as Bigint, i think that is creating the problem

Your Common Sense Over a year ago

bigint cannot be an issue. this field can store int numbers all right

Philip Bevan · Accepted Answer · 2012-03-08 11:53:08Z

-1

You can pull a date format from your mysql database and then when converting to a php variable you can use:

$num=mysql_numrows($result);

$i=0;
while ($i <= $num) {
$date=mysql_result($result,$i,"Date");
$date = date('d-m-Y', strtotime($date));
echo $date."<br>";
}

Where $result is your mysql query result and "Date" is the name of the column. However I am unsure of using this method with the time.

--EDIT--

link: http://php.net/manual/en/function.date.php

edited Mar 8, 2012 at 11:53

answered Mar 8, 2012 at 11:20

Philip Bevan

3452 silver badges12 bronze badges

5 Comments

Philip Bevan Over a year ago

this method works, i did not copy any code and currently have this working on a website. This is my own. How is this answer offtopic? He is asking how to display a date from a MySQL date in a certain format and I have given a method of doing this.

Your Common Sense Over a year ago

he is NOT asking how to display a date from a MySQL date. READ THE QUESTION

Philip Bevan Over a year ago

He is asking how to output the date in a certain format "D/M/Y H:i" instead of "m/d/y H:i". I have done this excluding the time. He is asking how to display a date from a MySQL date in a certain format READ MY COMMENT

Your Common Sense Over a year ago

He asks not for the code. he has the code already. he asks why doesn't it work.

Philip Bevan Over a year ago

let us continue this discussion in chat

Collectives™ on Stack Overflow

PHP to MySQL date formatting issue

4 Answers 4

4 Comments

Comments

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related