469,356 Members | 1,893 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

MySQL DATETIME and PHP Dates

Hi,

Is there an easy way of formatting a MySQL DATETIME field in PHP. At the
moment I'm using the code below, as using the returned DATETIME value from
the database with the date() function doesn't seem to work. I know I could
use the MySQL function date_format to format it before its returned but I
use a simply query (e.g. SELECT * FROM TABLE WHERE colum = '$somevalue') and
a while loop to read all the variables in, so I don't think I can format it
before its returned.

$date1 = "2005-04-12 20:45:54";
$date_array = explode("-", $date1);
$year = $date_array[0];
$month = $date_array[1];
$day = $date_array[2];
$day = $day[0].$day[1];
$ts = mktime(0,0,0,$month, $day, $year);
$dateVal = date("j-M-y", $ts);
echo "<br>Date is: ".$dateVal;

Thanks,

T.
Jul 17 '05 #1
7 15959

Tony Clarke wrote:
Is there an easy way of formatting a MySQL DATETIME field in PHP. At the moment I'm using the code below, as using the returned DATETIME value from the database with the date() function doesn't seem to work. I know I could use the MySQL function date_format to format it before its returned but I use a simply query (e.g. SELECT * FROM TABLE WHERE colum = '$somevalue') and a while loop to read all the variables in, so I don't think I can format it before its returned.

$date1 = "2005-04-12 20:45:54";

Use the function strtotime() <http://www.php.net/strtotime>
echo '<br>Date is: ' . date('j-M-y',strtotime($date1));

Ken

Jul 17 '05 #2
NC
Tony Clarke wrote:

Is there an easy way of formatting a MySQL DATETIME field in PHP.


Yes. Check out the strtotime() function:

http://www.php.net/strtotime

Cheers,
NC

Jul 17 '05 #3
You could use the MySQL function UNIX_TIMESTAMP(column) to return a
PHP-compatible UNIX timestamp, which you could then pass as the second
parameter of the PHP date() function.

For example:

$row = mysql_fetch_assoc(mysql_query("SELECT UNIX_TIMESTAMP(datetime)
AS datetime_u FROM table LIMIT 1"));
$date = date('j-M-y', $row['datetime_u']);

Jul 17 '05 #4
Tony Clarke wrote on Wednesday 06 Apr 2005 23:37:
Is there an easy way of formatting a MySQL DATETIME field in PHP.


I've just solved this problem myself:

function reformatDate($datetime) {
// put date in universal format, no seconds
list($year, $month, $day, $hour, $min, $sec) = split( '[: -]',
$datetime);
return "$year-$month-$day at $hour:$min";
}
$dateA=$row['date'];
$theDate=reformatDate($dateA);

Watch the line wrap in there!!

HTH,
- QS Computing.

--
QS Computing
http://www.qscomputing.plus.com
po********@qscomputing.plus.com
Jul 17 '05 #5
On 2005-04-07, QS Computing <po********@qscomputing.plus.com> wrote:
Tony Clarke wrote on Wednesday 06 Apr 2005 23:37:
Is there an easy way of formatting a MySQL DATETIME field in PHP.


I've just solved this problem myself:

function reformatDate($datetime) {
// put date in universal format, no seconds
list($year, $month, $day, $hour, $min, $sec) = split( '[: -]',
$datetime);
return "$year-$month-$day at $hour:$min";
}
$dateA=$row['date'];
$theDate=reformatDate($dateA);


Or you could use the builtin functions for date/time handling:

$timestamp = "2005-04-06 15:43:34";
$time = strtotime($timestamp);
print date('Y-m-d \a\t H:i', $time)."\n";

The strtotime() function will parse the input and give you a unix
timestamp. The date() function is used to format unix timestamps in a
variety of ways.

--
Cheers
- Jacob Atzen
Jul 17 '05 #6
Jacob Atzen wrote on Thursday 07 Apr 2005 19:25:
Or you could use the builtin functions for date/time handling


Hadn't even thought of that. Presumably using builtins speeds it up - I'll
change my code now.

Thanks,
- QS Computing.

--
QS Computing
http://www.qscomputing.plus.com
po********@qscomputing.plus.com
Jul 17 '05 #7
On 2005-04-07, QS Computing <po********@qscomputing.plus.com> wrote:
Jacob Atzen wrote on Thursday 07 Apr 2005 19:25:
Or you could use the builtin functions for date/time handling


Hadn't even thought of that. Presumably using builtins speeds it up -
I'll change my code now.


Maybe so, maybe not. I doubt you'll be formatting that many dates that
it will actually make a difference speedwise. But it does make it easier
to parse and format dates in a variety of formats.

--
Cheers
- Jacob Atzen
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Phil Powell | last post: by
5 posts views Thread by Bob | last post: by
2 posts views Thread by pholck | last post: by
2 posts views Thread by Kenneth P | last post: by
3 posts views Thread by laredotornado | last post: by
6 posts views Thread by Brandon | last post: by
2 posts views Thread by electronik | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.