converting timestamp...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

converting timestamp...

Post 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!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

20031117125000 is not a correct UNIX timestamp

Mark
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

but you do understand that it isn't a UNIX timestamp!? i.e. number of second since EPOCH (1st Jan 1970)

Mark
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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)
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post 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 :?:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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";
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post by mudkicker »

thanks!!!! :) :)
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post 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???
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
mudkicker
Forum Contributor
Posts: 479
Joined: Wed Jul 09, 2003 6:11 pm
Location: Istanbul, TR
Contact:

Post by mudkicker »

ok thanks for your help people! really appreciated.
Post Reply