format date and time from mySql

sd1

hello!
i have a field in mySql which called: myDate, it's type: dateime.

the mySql format: YYYY-mm-dd 00:00:00

i want to show it in php like that: dd/mm/yy 00:00

how can i do that?

EpicEro

The way that you are saving date will force you to pick apart the string to format the date you want as follows:

<?
$link=connect();
$query="select date from tablename";
$result=mysql_query ($query, $link);
while ($row=mysql_fetch_object($result)) {
	$date=explode("-", $row->date);
	$day=explode(" ", $date[2]);
	$year=substr($date[0],3,2);
	$time=explode(":", $day[1]);
	$thisdate=$day[0]."/".$date[1]."/".$year." ".$time[0].":".$time[1];
	echo $thisdate."<br>";
}
?>

You should try using a timestamp for dates, they are much easier to format when you pull them out of your database

<?
$link=connect();
$query="select date from tablename";
$result=mysql_query ($query, $link);
while ($row=mysql_fetch_object($result)) {
	$thisdate=date("m/d/y H:i",$row->date);  

	echo $thisdate."<br>";
}
?>

Hope this helps

trooper

The above post will do it.... however there is an easier way.

That is to get mysql to actually return the date in the format you want. Take a look below

SELECT .....

DATE_FORMAT(field_name,'%d/%m/%Y') as 'alias_name',

The important bit here is the DATE_FORMAT (its a mysql function not a php one) function. I suggest that you look up the function - here there are loads and loads of ways that the time and date can be retrieved from the database

simply sustitute field_name and alias_name for the appropriate values.

Where possible you should always try and get the DB to do as much of the processing as possible 🙂

HTH

sd1

i found an easier solution

$format_date =  strftime("%d/%m/%Y %H:%M" ,strtotime($rowShoppingCart['addDate']));

trooper

sd1 that is a good method - the only difference is that the db is not returning the format that you want

In your example you are still having to query the db and then preform some function on it

Anyway never mind - hope the starter of this thread gets the idea

cgraz

i agree with trooper here. I use DATE_FORMAT() to return the proper format, but it's up to you. Some people would code a list of 1 - 20 like this:

echo "1<br>";
echo "2<br>";
echo "3<br>";
echo "4<br>";
echo "5<br>";
echo "6<br>";
echo "7<br>";
echo "8<br>";
echo "9<br>";
echo "10<br>";
echo "11<br>";
echo "12<br>";
echo "13<br>";
echo "14<br>";
echo "15<br>";
echo "16<br>";
echo "17<br>";
echo "18<br>";
echo "19<br>";
echo "20<br>";

Others, would code it like this

for($i=0; $i <21; $i++) {
   echo $i . "<br>";
}

Both work, its just preference.

Cgraz

hardjoydk

I am building a simple news database using PHP and MySQL. I'm very new to both. So far I'm learning how to retrieve the data from the database in my php script, and present them in the way I want.

The field 'dato' contains the timestamp value, which I want to present in this manner (example) : '12. November 2003 - kl. 13:33' .

Is there an easy way to retrieve the timestamp value of my 'dato' field and present it in the above manner, within the current loop, which presents the entire content of the database?

This is the script which retrieves the news posts, sorts them by date, and presents them on the screen :

<?php // Etabler link $link til historie_db

$link = mysql_connect("db.sdu.dk", "login", "password");
if (!$link or !mysql_select_db("historie_db")) die("Der kan ikke etableres forbindelse til databasen");

$result = mysql_query("SELECT * FROM nyheder ORDER BY dato DESC", $link);

while ($query_data = mysql_fetch_array($result))

{

echo "",$query_data["titel"]," ";

echo "Postet af ",$query_data["afsender"]," / ";

echo $query_data["dato"]," ";

echo $query_data["tekst"]," ";

}

yuraupt

Hello hardjoydk,

using MySQL function DATE_FORMAT (I prefer this approach as well):

replace your SQL with this one:
SELECT
titel,
afsender
DATE_FORMAT(dato, '%d. %M %Y - kl. %H:%i') formated_dato,
tekst
FROM nyheder
ORDER BY dato DESC

and then output the date like that:
echo $query_data["formated_dato"],"

To find more about DATE_FORMAT and other MySQL data and time functions, please refer to the documentation:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Date_and_time_functions

hardjoydk

Thanx for the fast help 🙂 -works like a charm!

One more question :

Is there a simple way I can return (or convert) the english MySQL values of months and weekdays to those of my native language? -or will I have to make a separate script of some sorts to accomplish this job?

yuraupt

Then DATE_FORMAT would not help you...

I could recomend this way:

0) create arrays with names of all months and weekdays. It's better to have the arrays in some separate file which you will include to other scripts. Then you can easiely modify and reuse it.

In the script to output news:
1) fetch data from the database

2) obtain month and day from the date field somehow

3) format the output of the date field using those arrays

I hope you got the idea. Try to implement it yourself, if you fail then let me know and will come with an example.

hardjoydk

Thanx for the help, yuraupt 🙂 - Managed to get the job done with the following -posting it here in case others may find it useful. I'll put the arrays in a separate file, since I'll probably need it elsewhere too. Haven't implemented the weekdays yet, but I suppose I can retrieve that value with date_format as well, if necessary?

$query = "SELECT id, afsender, email, DATE_FORMAT(dato, '%e %m %Y %H %i')formated_dato, titel, subtitel, tekst, link, linktekst FROM nyheder ORDER BY dato DESC, id DESC";

$result = mysql_query($query, $link);

while ($query_data = mysql_fetch_array($result))
{
$id = $query_data["id"];
$afsender = $query_data["afsender"];
$email = $query_data["email"];
$dato = $query_data["formated_dato"];

//måneder konverteret til danske måneder

$temp = explode(" ", $dato);

$months = array ('01' => januar, '02' => februar, '03' => marts, '04' => april, '05' => maj, '06' => juni, '07' => juli, '08' => august, '09' => september, '10' => oktober, '11' => november, '12' => december);

$ugedage = array ('01' => mandag, '02' => tirsdag, '03' => onsdag, '04' => torsdag, '05' => fredag, '06' => lørdag, '07' => søndag);

while(list($key, $val) = each($months))
{
if ($temp[1] == $key)
{
$month = $val;
}
}

$dato = "$temp[0]. $month $temp[2] kl.$temp[3]:$temp[4]";
$titel = $query_data["titel"];
$subtitel = $query_data["subtitel"];
$tekst = $query_data["tekst"];
$link = $query_data["link"];
$linktekst = $query_data["linktekst"];

echo "$titel postet af <a href=mailto:$email class='head2'>$afsender</a> $dato $tekst<a href=$link class='text'> $linktekst</a> ";
}

yuraupt

Excellent!!!

looks nice as for me 😉