as u know it's like 20031117125000 and i want it dd.mm.yy hh.mm.ss
converting timestamp...
Moderator: General Moderators
- mudkicker
- Forum Contributor
- Posts: 479
- Joined: Wed Jul 09, 2003 6:11 pm
- Location: Istanbul, TR
- Contact:
converting timestamp...
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!
as u know it's like 20031117125000 and i want it dd.mm.yy hh.mm.ss
- mudkicker
- Forum Contributor
- Posts: 479
- Joined: Wed Jul 09, 2003 6:11 pm
- Location: Istanbul, TR
- Contact:
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;
}
?>you can use date_format() sql function:
Bech, internally timestamp columns stored as unix timestamps, but MySQL autoformats it.
Code: Select all
select date_format(sometimestamp,'%d.%m.%y %H.%i.%s') from sometable;Hey, I have timestamp in my mysql table. I'm on windows though, anyhow, i can't use:
I get:
but that gave me no error nor result at all.
thanks,
-Nay
Code: Select all
SELECT * FROM news DATE_FORMAT(date,'%d.%m.%y %H.%i.%s') FROM news ORDER BY id DESC LIMIT 10my mysql server version is 4.0.16. i also tried: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
Code: Select all
SELECT DATE_FORMAT(date,'%d.%m.%y %H.%i.%s') FROM news ORDER BY id DESC LIMIT 10thanks,
-Nay
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)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";i'm having to do this:
wierd O_o and when i tried this:
it gave me: 19-01-38
-Nay
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];Code: Select all
$date = "20031011112355";
$date = date("d-m-y", $date);
echo $date;-Nay
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Nay, you can make your life easier by using an alias:
Also remember, you can't use date() to format a MySQL timestamp because date() only be used to format UNIX timestamps...
Mac
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'];Mac
- mudkicker
- Forum Contributor
- Posts: 479
- Joined: Wed Jul 09, 2003 6:11 pm
- Location: Istanbul, TR
- Contact:
well if i want to select all the tables but this timestamp column (edited of course) too what should i use??
how should this be???
Code: Select all
SELECT * FROM news ORDER by news_id DESC- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
You should always name all the columns you wish to return, it's best not to use *, so you can do:
Mac
Code: Select all
SELECT field1, field2, field3, field4, DATE_FORMAT(datefield, '%d.%m.%Y') AS datefield FROM NEWS ORDER BY news_id DESC