[SOLVED] echo timestamp as dd/mm/yyyy

[runnerjp]

hey guys...is it possible to chnage the timestamp in my db from yyyy/mm/dd to dd/mm/yyyy as in echoing it

[obsidian]

By default, the date fields in mysql are stored as YYYY-MM-DD, but any human readable format can be translated via the strtotime() function:

<?php
$ts = '2008-04-09';
echo date('m/d/Y', strtotime($ts));
?>

[runnerjp]

humm ok but how can i pull it from my db and chnage it lol because i wnat it to go with this

echo "<tr><td><p>Subject: <b>$subject</b></p></td></tr><tr><td><p><i>Posted by: $sent_by on $sent_on</i></p>
</td></tr><tr><td><p>$message<br><br></p></td></tr>";

  where sent_on is the date that needs to be m/d/y

[paul2463]

or if you are pulling them from the database you could use:-

$query = "SELECT DATE_FORMAT(dateColumn,'%c-%d-%Y') as FDate from tablename";

[obsidian]

humm ok but how can i pull it from my db and chnage it lol because i wnat it to go with this

echo "<tr><td><p>Subject: <b>$subject</b></p></td></tr><tr><td><p><i>Posted by: $sent_by on $sent_on</i></p>
</td></tr><tr><td><p>$message<br><br></p></td></tr>";

  where sent_on is the date that needs to be m/d/y

 

What is the actual data type in the database? Is it a true DATE or DATETIME field? If so, you can either use the DATE_FORMAT function within MySQL to retrieve it in the desired format, or you can do what I showed in the first post to the raw returned value.

[runnerjp]

the data in the db is just timestamp  output like so 2008-04-10 15:33:30

 

im a little biwilderd lol of how u get it and show it on the page tho lol

[paul2463]

read my first post and also read the mysql manual on the DATE_FORMAT() function

[eRott]

In my opinion, the best way to store time and date in your DB is with a unix timestamp.

 

EXAMPLE:

Today's Date: Thursday, April 10th, 2008, 10:20AM GMT

Today's Date in a Unix Timestamp: 1207822822

 

to generate the unix timestamp before saving it to your database

<?php
$time = time(); // make the variable whatever you want and store it in the database
?>

Note: Make sure the field in your DB is set to INT(11) (by which I mean make sure the type is INT and the length/value is 11).

 

to grab the time from the DB

<?php
$query = "SELECT * FROM table_name_here"; // note: use the WHERE clause to grab the date for the correct row
$result = mysql_query($query) or die(mysql_error());
$grab = mysql_fetch_array($result) or die(mysql_error());
$date = $grab['date']; // make sure you replace 'date' with whatever your field name is that holds the unix timestamp
?>

 

format the date from

<?php
$sent_on = date("M jS, Y \a\\t h:i A T", $date); // will produce an output that looks like: Apr 10th, 2008 at 10:20 AM GMT
?>

NOTE: For more information on how to format the date, see HERE.

 

now echo whatever you wanted to echo

<?php
echo "<tr><td><p>Subject: <b>".$subject."</b></p></td></tr><tr><td><p><i>Posted by: ".$sent_by." on ".$sent_on."</i></p>
</td></tr><tr><td><p>".$message>."<br><br></p></td></tr>";
?>

 

That should give you somewhere to start. I just wrote it up right quick so don't shoot me if it doesn't work at first.

 

Take care.

[runnerjp]

ah ok i used this to record my time $timedate = time();

 

and then this to output it ".$sent_on."</i> - Date: ".date("D m Y",$datetime)."

 

but my output is Date: Thu 04 2008  how can i make it look like erotts way of

format the date from

Code:

 

$sent_on = date("M jS, Y \a\\t h:i A T", $post_date); // will produce an output that looks like: Apr 10th, 2008 at 10:20 AM GMT

[obsidian]

the data in the db is just timestamp  output like so 2008-04-10 15:33:30

 

im a little biwilderd lol of how u get it and show it on the page tho lol

 

That is a DATETIME column. So, to display it with PHP, you could just do this:

<?php
$sql = mysql_query("SELECT date_column FROM myTable");
while ($row = mysql_fetch_assoc($sql))
{
  $formatted = date('Y-m-d', strtotime($row['date_column']));
  echo "$formatted<br />\n";
}
?>

 

...or, let MySQL do the formatting...

<?php
$sql = mysql_query("SELECT DATE_FORMAT(date_column,'%c/%e/%Y') AS formatted FROM my_table");
while ($row = mysql_fetch_assoc($sql))
{
  echo "{$row['formatted']}<br />\n";
}
?>

[obsidian]

In my opinion, the best way to store time and date in your DB is with a unix timestamp.

 

I would disagree with that sentiment. There is a reason that MySQL has date and time types. It's much better off to learn the tools and use them as intended. It will really save you time in the long run.

[runnerjp]

humm im confused now lol  how about i post my 2 scripts to give u guys a better picture lol

 

<?php
session_start(); 
require_once '../settings.php';
	checkLogin ('1');

$qProfile = "SELECT * FROM sysmsg ORDER BY msg_id DESC";
$rsProfile = mysql_query($qProfile);

while($row = mysql_fetch_array($rsProfile)){
$msg_id = $row['msg_id'];
$sent_on = $row['sent_on'];
$sent_by = $row['sent_by'];
$subject = $row['subject'];
$message = $row['message'];
$datetime = $row['sent_date'];

echo("<p>Subject: <b>".$subject."</b></p><p><i>Posted by:".$sent_by." on ".$sent_on."</i> - Date: ".date("D m Y",$datetime)."</p>
<p>".$message."<br><br></p>");
}
mysql_close();

if($id == 1){
echo "<a href=\"http://www.runningprofiles.com/members/index.php?page=admin\">Admin Index</a>\n";
}

?>

 

<a href="../index.php">Back to index</a><br>
<br>
<?php
include("connect.php");

// $msg_id should not change - do not let the user change this, use the message id to find the message you are wanting to edit.
$msg_id = mysql_escape_string($_POST['msg_id']);
$sent_by = mysql_escape_string($_POST['sent_by']);
$subject = mysql_escape_string($_POST['subject']);
$message = mysql_escape_string($_POST['message']);

// This gives the unix timestamp of the current time down to the second.
$timedate = time();

// make sure $msg_id POSTED from the previous page is an id that you want to change. ALSO make sure `sent_date` field exists.
$update = "UPDATE `sysmsg` 
SET 
`sent_by`='$sent_by',
`subject`='$subject',
`message`='$message',
`sent_date`='$timedate'

WHERE `msg_id`='$msg_id'
";
$rsUpdate = mysql_query($update) or die("Mysql Query Error For:<br />".$update."<br /><br />MySQL Said: ".mysql_error());
if (mysql_affected_rows() >= 1)
{
   echo "Update successful.";
}else{
   echo "No rows were updated";
}
mysql_close();
?>

 

btw im intrested in the huge response allready lol and how many ways people go for

[eRott]

ah ok i used this to record my time $timedate = time();

 

and then this to output it ".$sent_on."</i> - Date: ".date("D m Y",$datetime)."

 

but my output is Date: Thu 04 2008  how can i make it look like erotts way of

format the date from

Code:

 

$sent_on = date("M jS, Y \a\\t h:i A T", $date); // will produce an output that looks like: Apr 10th, 2008 at 10:20 AM GMT

 

Because you only chose to show the Day, the Month and the year.

 

D = (A textual representation of a day, three letters)

m = (Numeric representation of a month, with leading zeros)

Y = (A full numeric representation of a year, 4 digits)

 

Aside from that, you have both the variable in that echo line and just the plain date function. You can use it either way, I just prefer to store it in a variable.

 

<?php
$sent_on = date("M jS, Y \a\\t h:i A T", $date);
echo "</i> - Date: ".$sent_on;
?>

[eRott]

Okay, replace your first chunk of code with this:

 

<?php
session_start(); 
require_once '../settings.php';
	checkLogin ('1');

$qProfile = "SELECT * FROM sysmsg ORDER BY msg_id DESC";
$rsProfile = mysql_query($qProfile);

while($row = mysql_fetch_array($rsProfile)){
$msg_id = $row['msg_id'];
$sent_on = $row['sent_on'];
$sent_by = $row['sent_by'];
$subject = $row['subject'];
$message = $row['message'];
$datetime = $row['sent_date'];

$sent_on = date("M jS, Y \a\\t h:i A T", $datetime);

echo("<p>Subject: <b>".$subject."</b></p><p><i>Posted by: ".$sent_by." on ".$sent_on."</i></p>
<p>".$message."<br><br></p>");
}
mysql_close();

if($id == 1){
echo "<a href=\"http://www.runningprofiles.com/members/index.php?page=admin\">Admin Index</a>\n";
}

?>

[eRott]

In my opinion, the best way to store time and date in your DB is with a unix timestamp.

 

I would disagree with that sentiment. There is a reason that MySQL has date and time types. It's much better off to learn the tools and use them as intended. It will really save you time in the long run.

 

I can understand and respect your opinion, but it's just like the news I suppose. Just because some story is told on the news on T.V., doesn't mean you have to believe it. Same thing (sort of) can be applied to your statement. Just because MySQL offers a way to store date and time, doesn't always mean it's the best way.

[runnerjp]

ahhhhh awsome ty for all your help guys and very fast response

 

ty erott for typin out code for me aswell

 

nice to see there are always more then 1 way of doing things...the harder 1 is which to choose lol

[obsidian]

I can understand and respect your opinion, but it's just like the news I suppose. Just because some story is told on the news on T.V., doesn't mean you have to believe it. Same thing (sort of) can be applied to your statement. Just because MySQL offers a way to store date and time, doesn't always mean it's the best way.

 

I agree that it may not always be the best, but it goes beyond opinion when you look at processing and optimization of scripts. What I am recommending is for someone to make themselves a better coder by learning to use the tools properly, and then they can make their own decision on how to do it based on each individual situation.

[eRott]

I can understand and respect your opinion, but it's just like the news I suppose. Just because some story is told on the news on T.V., doesn't mean you have to believe it. Same thing (sort of) can be applied to your statement. Just because MySQL offers a way to store date and time, doesn't always mean it's the best way.

 

I agree that it may not always be the best, but it goes beyond opinion when you look at processing and optimization of scripts. What I am recommending is for someone to make themselves a better coder by learning to use the tools properly, and then they can make their own decision on how to do it based on each individual situation.

 

I do not understand how using some built in MYSQL function fails to make me a good coder. I see no reason why using a timestamp over the built in MySQL date and time functions would be any less productive. It's not like I am misusing MySQL's date and time tools. It's simply option A and option B, two different means of accomplishing the same thing. Whether you pour your pop (or soda) in a glass or leave it in the can, the drink it still going to be drunk as quickly or as slowly either way.

 

You keep essentially telling me the use of MySQL's functions for date and time is better then using the method I chose. Care to explain to me exactly how it's better? By all means, I'm open to change. But from past experience, I found using the built in MySQL functions nothing more then a hassle. Show me why it's better.

[obsidian]

You keep essentially telling me the use of MySQL's functions for date and time is better then using the method I chose. Care to explain to me exactly how it's better? By all means, I'm open to change. But from past experience, I found using the built in MySQL functions nothing more then a hassle. Show me why it's better.

 

As I mentioned before, I am not trying to convince someone to change their opinions about something as much as trying to help developers hone their skills and have an understanding of the different options. Now, to answer your question, yes, in most cases it is much better to use the appropriate tools for the job, and it is not always simply options A and B to consider.

 

Consider the date and time function limitations within PHP before 5.1, for instance. With those limitations, a user can only handle dates from 1970 through until the present, because the negative integers were not handled appropriately to calculate back before the UNIX epoch. Now, with your recommended solution, no issues are encountered in the processing of an ordering script because people can obviously not place an order in the past. So, what happens when a user gets into the habit of that method and then has to deal with ages on a PHP4 install? Then, like it or not, they will need to know of another appropriate tool to use. In this case, we have a tool designed specifically for date and time calculations.

 

Besides the obvious limitation differences between the two methods, we come to the actual optimization process. Grant it, as someone is learning, the speed or optimization of a script is not priority #1, but if you ever get working on a site with millions of records and page hits, optimization becomes key, and when you are having to query for the data initially and then translate it with PHP, you'll find by running benchmark testing that in most cases, using the MySQL date/time functions, you will save yourself processing time since you can simply dump the results rather than having to parse them a second time.

 

In summary, and as I mentioned in my previous post, they are both legitimate ways to handle the problem at hand, but part of becoming proficient at any language is understanding where the limitations are or where tweaks can be made to optimize for a given situation.

 

Hope this helps clarify