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 ? :P :P :?:

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.