Page 1 of 2
converting timestamp...
Posted: Wed Nov 19, 2003 10:29 am
by mudkicker
Hi peopla do you know a function or any other way to convert MySQL timestamp to a d-m-Y format??
as u know it's like
20031117125000 and i want it dd.mm.yy hh.mm.ss

thanks!
Posted: Wed Nov 19, 2003 10:33 am
by JayBird
20031117125000 is not a correct UNIX timestamp
Mark
Posted: Wed Nov 19, 2003 10:34 am
by mudkicker
Code: Select all
<?php
function myts_date($format,$mytimestamp)
{
$month = substr($mytimestamp,4,2);
$day = substr($mytimestamp,6,2);
$year = substr($mytimestamp,0,4);
$hour = substr($mytimestamp,8,2);
$min = substr($mytimestamp,10,2);
$sec = substr($mytimestamp,12,2);
$epoch = mktime($hour,$min,$sec,$month,$day,$year);
$date = date ($format, $epoch);
return $date;
}
?>
i found it thanks anyway.
Posted: Wed Nov 19, 2003 10:36 am
by JayBird
but you do understand that it isn't a UNIX timestamp!? i.e. number of second since EPOCH (1st Jan 1970)
Mark
Posted: Wed Nov 19, 2003 12:04 pm
by Weirdan
you can use date_format() sql function:
Code: Select all
select date_format(sometimestamp,'%d.%m.%y %H.%i.%s') from sometable;
Bech, internally timestamp columns stored as unix timestamps, but MySQL autoformats it.
Posted: Wed Nov 19, 2003 12:52 pm
by Nay
Hey, I have timestamp in my mysql table. I'm on windows though, anyhow, i can't use:
Code: Select all
SELECT * FROM news DATE_FORMAT(date,'%d.%m.%y %H.%i.%s') FROM news ORDER BY id DESC LIMIT 10
I get:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_FORMAT(date,'%d.%m.%y %H.%i.%s') FROM news ORDER BY id DES
my mysql server version is 4.0.16. i also tried:
Code: Select all
SELECT DATE_FORMAT(date,'%d.%m.%y %H.%i.%s') FROM news ORDER BY id DESC LIMIT 10
but that gave me no error nor result at all.
thanks,
-Nay
Posted: Wed Nov 19, 2003 1:28 pm
by Weirdan
First query is definitely wrong, the second one looks correct to me. Could you post your db scheme? I've just tried this:
Code: Select all
mysql> use test
Database changed
mysql> drop table tst;
Query OK, 0 rows affected (0.02 sec)
mysql> create table tst(q timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tst values(now());
Query OK, 1 row affected (0.00 sec)
mysql> select date_format(q,'%d.%m.%y %H.%i.%s') from tst;
+------------------------------------+
| date_format(q,'%d.%m.%y %H.%i.%s') |
+------------------------------------+
| 19.11.03 21.29.01 |
+------------------------------------+
1 row in set (0.00 sec)
Posted: Wed Nov 19, 2003 3:13 pm
by mudkicker
thanks people.
some replies is very useful, too.
my next question would be: how can i take it from mysql query without converting it with PHP ?

Posted: Wed Nov 19, 2003 5:40 pm
by Weirdan
Assuming you have the table tst:
Code: Select all
mysql_query("select date_format(q,'%d.%m.%y %H.%i.%s') from tst");
list($date)=mysql_fetch_row();
echo $date."\n";
Posted: Wed Nov 19, 2003 6:01 pm
by mudkicker
thanks!!!!

Posted: Wed Nov 19, 2003 9:34 pm
by Nay
i'm having to do this:
Code: Select all
$q = mysql_query("select date_format(date,'%H:%i, %d-%m-%y') from news order by id desc", $con) or die(mysql_error());
$row = mysql_fetch_array($q);
$sql = "date_format(date,'%H:%i, %d-%m-%y')";
echo $row[$sql];
wierd O_o and when i tried this:
Code: Select all
$date = "20031011112355";
$date = date("d-m-y", $date);
echo $date;
it gave me: 19-01-38
-Nay
Posted: Thu Nov 20, 2003 4:36 am
by twigletmac
Nay, you can make your life easier by using an alias:
Code: Select all
$sql = "SELECT DATE_FORMAT(date,'%H:%i, %d-%m-%y') AS formatted_date FROM news ORDER BY id DESC";
$q = mysql_query($sql, $con) or die(mysql_error().'<p>'.$sql.'</p>');
$row = mysql_fetch_array($q);
echo $row['formatted_date'];
Also remember, you can't use date() to format a MySQL timestamp because date() only be used to format UNIX timestamps...
Mac
Posted: Thu Nov 20, 2003 4:43 am
by mudkicker
well if i want to select all the tables but this timestamp column
(edited of course) too what should i use??
Code: Select all
SELECT * FROM news ORDER by news_id DESC
how should this be???
Posted: Thu Nov 20, 2003 5:14 am
by twigletmac
You should always name all the columns you wish to return, it's best not to use *, so you can do:
Code: Select all
SELECT field1, field2, field3, field4, DATE_FORMAT(datefield, '%d.%m.%Y') AS datefield FROM NEWS ORDER BY news_id DESC
Mac
Posted: Thu Nov 20, 2003 9:24 am
by mudkicker
ok thanks for your help people! really appreciated.