Formatting date and time

[erikla]

I have selected datetime values from a MySQL database (originally stored with the NOW() method) and want to present them on a page formatted in a specific way. I succeded in making it work with the following php code:

$time = date('d/m Y H:i', strtotime($row['time']));
echo $time;

I am however unsure if this is the best way to do it? I noticed some comments at php.net stating that this method has some limitations due to UNIX time. I am confused. There seem to be many ways of formatting date and time. Maybe somebody can clarify the situation, and tell me what will be appropriate in the current situation? I will appreciate any suggestions ...

 

Erik 

Edited July 17, 2014 by erikla

[mac_gyver]

mysql has a DATE_FORMAT() function that allows you to do this directly in your sql query - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

 

the problem with using  strtotime() and date() is they aren't just changing the format of the information, they are doing a conversion to/from a unix timestamp as a php integer value, which has a serious problem with the range of values it can represent that varies with the php version and the maximum integer size that the system php is running on supports. this limits the year that can be represented to either 1901 or 1970 on the low end and 2038 on the high end.

 

php's date/time functions that don't rely on php integer values as inputs/outputs should not have this problem (they are supposed to store values internally as a 64-bit number.)

[erikla]

Thanks! Your reply certainly addresses some of my concerns about using the strtotime() and data() functions. So I understand you advice me to do the formatting directly on the MySQL level, right?. What will the syntax be, if I may ask more detailed? At the moment my code for inserting into my database is (using PDO):

$stmt = $db->prepare("INSERT INTO guestbook(`time`, `ip`, `name`, `email`, `contribution`) VALUES(NOW(), ?, ?, ?, ?)");
$stmt->execute(array($ip, $name, $email, $contribution));

Regards,

 

Erik

[Psycho]

1. Why not set the 'time' field to have a default value of NOW()? Then you don't need to include the field in your INSERT query at all!

 

2. You do not need to do anything with the INSERT statements. You need to modify your SELECT statement to have the DB format the date when it returns it to you

 

3. mac_gyver provided a link to to manual page that explains how to format the data in MySQL. Is there something on that page you don't understand?

[Barand]

Or make the "time" field type TIMESTAMP then it will auto update without you having to insert anything

[Psycho]

Or make the "time" field type TIMESTAMP then it will auto update without you having to insert anything

 

Yeah, if he is setting it to NOW() then it should be a timestamp and not a datetime anyway. But, a timestamp will, by default also update the the current timstamp when the record is updated (again, without having to include it in the query). If that's not the behavior needed, then the default property needs to be modified to only populate on creation.

[erikla]

You are right, Psycho, that the formatting shouldn't take place when inserting in the database, but rather when selecting from it. My mistake! Here is the output from my database (guestbook) with the following fields: id, time, ip, name, email and contribution. I had a hard time figuring the syntax when including the datetime-formatting into the select statement. I hope it looks OK now? At least it works. I have named the formatted time field 'ftime'.

 

By the way: Is there a way to turn the select statement into a prepared statement? I guess you guys prefer these ...

try {
  $db = new PDO('mysql:host='.$server.';dbname='.$database.';charset=utf8mb4', $username, $password);  //Connecting to database
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

  $sql = "SELECT id, name, email, date_format(time, '%e/%c %Y, %H:%i') as ftime, contribution FROM guestbook order by time";
  foreach ($db->query($sql) as $row) {
	echo "<hr>";
	echo "Contribution number: ".$row['id']."<br/>";
	echo "Time: ".$row['ftime']."<br/>";
	echo "Name: ".$row['name']."<br/>";
	echo "Email: ".$row['email']."<br/><br/>";	
	echo nl2br($row['contribution'])."<br/>";
	
  $db=NULL;  //Closing database	
  }
} catch(PDOException $ex) {
	echo "An error ocurred";
}

Sorry that my questions more and more have turned into a MySQL question. Initially I thought it would be a php question ...

 

Erik

[Psycho]

By the way: Is there a way to turn the select statement into a prepared statement? I guess you guys prefer these ...

 

I suppose you could, but why?

 

http://en.wikipedia.org/wiki/Prepared_statement

 

 

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.

 

There are no variable values in that query which are substituted. Making that a prepared statement has no purpose.

[erikla]

I see your point that there are no variable values to be substituted, but maybe still it would have the advantage of being faster, since the sql statement will be compiled first. I am especially thinking about the formatted datetime. Maybe I am totally wrong here. I am not an expert. That's why I am asking.

 

Erik

Edited July 18, 2014 by erikla

[Psycho]

You may want to take some time to really understand what prepared statements are any why you should use them, There is NO reason to use a prepared statement for a static query. In fact, even if you have variable data in the query, if you are running it one time - the prepared query would be slower because there is overhead in creating the prepared query before running it. But, that overhead is more than made up for by the fact that it will be much more secure. But, if you are running the query multiple times with different values then you do get a performance increase.

 

But, for a static query, there is no benefit or valid reason to make is a prepared statement. The whole point of prepared statements is to pass them values.