help with date()

[CyberShot]

I have a date in my database like so

 

2015-09-08

 

I am trying to format it with the date() function like this

date('m-d-y', $row['date'])

but it's returning 01-01-70 for every date. If I dump $row['date'] I get 2015-09-08

[Jacques1]

Check the manual. The date() function expects a Unix timestamp (i. e. an integer) as the second argument.

 

The hacky approach would be to pass your input date to strtotime(), hope that this function will recognize the format and then pass the resulting Unix timestamp to date().

 

The correct approach is to actually parse the input date using the date format of MySQL and then reformat the date.

[benanamen]

You can get it straight from the database the way you want it.

SELECT date_format(date_column, '%m/%d/%Y') as date

@Jaques1, why go through code gymnastics to format the date when the DB can easily give it to you the way you want? Surprised you didn't mention date_format.

Edited November 25, 2015 by benanamen

[CyberShot]

This problem is playing tricks on my. I just updated my code to this

$date = date('m-d-y', strtotime($row['date']));

when I refreshed my screen, the date worked. Then I insert a new record and all the of the sudden it no longer works. I thought about getting it straight from the database as you suggested Benanamen, but that would require another query. I would rather get it done with php.

[benanamen]

It doesn't require another query. Post your current query.

[CyberShot]

My current query

$result = $myconn->query("SELECT * FROM stats");

Well, I figured it out. Seems I need to do it twice. In my html form, I have this

$purchase_date = date('d-m-y', strtotime($_POST['date']));

and then when I fetch the results, I do this

while($row = mysqli_fetch_assoc($result)){
    $date = date('m-d-y', strtotime($row['date']));
    echo $date;
}

Edited November 25, 2015 by CyberShot

[Jacques1]

@Jaques1, why go through code gymnastics to format the date when the DB can easily give it to you the way you want? Surprised you didn't mention date_format.

 

Rendering data is the job of the application.

 

As soon as the OP realizes that there's a world outside of the USA (just kidding), the date format becomes user-dependend. With your approach, that means he'll have to assemble the format in the application, then pass it to the database in every single query to get pre-rendered dates and finally display the dates. It makes a lot more sense to simply get the dates and then render them in whatever way he likes.

Edited November 25, 2015 by Jacques1

[CyberShot]

@Guru

 

I know that when you are developing a database for the web, you might use a date format that is good for everyone. A universal format. this database is for my own personal use that will not be live on the internet. I am running it on my own machine using wamp. So I like the date format that I have been using for 35 years. It's also good practice to learn how to manipulate the date

[Jacques1]

Your date format is fine. The reply was directed more towards benanamen.

[benanamen]

OP, while I am pondering the response from @Jaques1, the SQL I said would not require two query's is simply

 

SELECT

column1,

column2,

date_format(date_column, '%m/%d/%Y') as date 

FROM stats

 

Regardless of the date formatting, better to select the specific column names you want rather than  SELECT *

[benanamen]

@Jaques1,

I am not sure I am getting what you said as to why. Could you please provide a case example.

 

* And if there are worlds outside the USA, they should just be using our date format anyways.

Edited November 25, 2015 by benanamen

[Jacques1]

@Jaques1,

I am not sure I am getting what you said as to why. Could you please provide a case example.

 

First off: If the OP chooses your approach, that means he has to go through every single date expression in every single SELECT query and replace the expression with DATE_FORMAT(date, <hard-coded format>). That's already bad enough.

 

But it gets even worse if he decides to replace his personal date format with a different format to make his application ready for the Internet. Now he again has to go through all queries to change the hard-coded format. And that's still just a single hard-coded format. To make the format dynamic or support multiple formats, he needs a prepared statement only to pass the date format to the database system.

 

And what if the same date is used in different contexts with different formats? It's fairly common to fetch data once and then use it multiple times for all kinds of purposes.

 

That's why you should separate the data from the presentation. Rendering dates in different formats is a presentation issue and should be done by the application. The database system only provides an abstract calendar date (which happens to be stored in some SQL-specific format).

 

 

 

* And if there are worlds outside the USA, they should just be using our date format anyways.

 

Only if you accept the metric system.

[maxxd]

Why not just use a DateTime object and save the additional hassle?

$dt = '2015-09-08';
$dateObj = new DateTime($dt);
print("<p>".$dateObj->format('Y-m-d')."</p>");
print("<p>".$dateObj->format('M j, Y')."</p>");

[Jacques1]

That's the plan (see reply #2). Except that I'd explicitly parse the input date rather than relying on PHP to figure out the right format.

[benanamen]

 

 

I'd explicitly parse the input date rather than relying on PHP to figure out the right format.

 

Code sample please?

[CyberShot]

getting this right seems to be very difficult. I keep getting mixed results. Some dates are good, some it mixes up the month and date. I am not getting consistent results. I just tried using the date object and it is producing mixed results

<?php if(!empty($_POST)){ 
		$miles   =   $myconn->real_escape_string($_POST['miles']);
		$amount  =   $myconn->real_escape_string($_POST['amount']);
		$gallons =   $myconn->real_escape_string($_POST['gallons']);
		$purchase_date = date('d-m-y', strtotime($_POST['date']));
		
		$sql = "INSERT INTO `stats` (miles, amount, Gallons, date) VALUES ('$miles', '$amount', '$gallons', '$purchase_date')";
		
		$myconn->query($sql) or die($myconn->error);
		header('Location:index.php');
 } ?>


while($row = mysqli_fetch_assoc($result)){
   $date = str_replace('-','/', $row['date']);
   $dateob = new DateTime($date);
   $dt = $dateob->format('m-d-y');
echo "<tr><td id='". $row['id'] . "'>" . $row['miles'] . '</td><td>' . number_format($row['amount'], 2) . '</td><td> ' . $row['gallons'] . '</td><td> ' . $dt . "</td></tr>";
		}

I think at this point, I might just try getting it straight from the database with the query and tackle the issue at a later time.

[Jacques1]

<?php


const DATABASE_DATE_FORMAT = 'Y-m-d';




$input_date = '2015-11-25';


$utc = new DateTimeZone('UTC');

$date = DateTime::createFromFormat(DATABASE_DATE_FORMAT, $input_date, $utc);


$output_format = 'm-d-Y';

echo 'Formatted: '.$date->format($output_format);

[benanamen]

What is the format of $_POST['date'] and what is the DB date column type? If the column is standard mysql date type your insert format (d-m-y) is wrong. The mysql date format is Y-m-d.

Edited November 25, 2015 by benanamen

[CyberShot]

Well, I have input dates in my form like this 11/25/2015 and 11-25-2015. The data type in the database is set to "date". Although I just started thinking that maybe I should just change it to text. I don't want to do that because I am thinking that it needs to be a date type so that I can sort the returns by date.

 

I just started to realize that these date functions need to match the format of the data in the database by reading the comments here and a little of the documentation. I thought setting date(d-m-y) was formatting it to that format. I will try again and see what happens

[benanamen]

First thing, leave the column type as date.  Why are you using two different date formats in your form? Only one of the form inputs should be named date to correspond to $_POST['date'].

 

 

11-25-2015  and /or 11/25/2015 needs to be transformed to 2015-11-25 to insert into your date column. There are several ways to do that. Bottom line the format needs to be YYYY-MM-DD to insert into the date column.

Edited November 25, 2015 by benanamen

[benanamen]

@Jaques1, you said

 

The hacky approach would be to pass your input date to strtotime(), hope that this function will recognize the format and then pass the resulting Unix timestamp to date().

 

Would that be in reference to the following and if so why is it hacky? Seems you may be referring specifically to Unix timestamp. In regards to unix timestamp, does  strtotime() have some issue with working sometimes and sometimes not?

$date = date("Y-m-d",strtotime($_POST['date']));

Edited November 25, 2015 by benanamen

[Barand]

I would recommend using a datepicker component in your form, if you aren't already. Expecting all users to enter consistent date formats will lead to disappointment, also you get confusion with text field dates. Is 06-07-2015 in d-m-Y format or is it m-d-Y? With a datepicker you can display (and post) the same format every time. I usually set my datepickers to display 07-Jun-2015 format which is universally understood by users and is unambiguous. What's more it unambiguous and understood by the PHP strtotime() function and DateTime class.

$d = new DateTime('07-Jun-2015');
echo $d->format('Y-m-d');  // 2015-06-07

echo date('Y-m-d', strtotime('07-Jun-2015'));  // 2015-06-07

[CyberShot]

I added the date picker to the code using jquery. Does the date need to enter the database as a "Date" type? Right now, it's type is coming from a textbox with the type of text.

[benanamen]

Thats fine, as long as the format is correct by the time it reaches the insert.

[CyberShot]

I think that was a big problem. It took me several hours throughout the entire day to decide that the date needs to be formatted going into the database. So getting it from my html form I have formatted it like so

$purchase_date = date('Y-m-d H:i:s', strtotime($_POST['date']));

Now it's in the database in it's correct format as Y-M-D. Until I did that, it was returning 0000-00-00. So now I need to format it for my American eyes as M-D-Y as it's coming out of the database