Page 1 of 1

timestamping

Posted: Mon Apr 19, 2004 8:46 am
by tiresome
This is how I created my table in MySql


create table guestBook
(
entry_id integer not null auto_increment,
name varchar(40) null,
location varchar(40) null,
email varchar(40) null,
url varchar(40) null,
comments text null,
created timestamp,
remote_addr varchar(20) null
, key guestbook_key (entry_id)
);

The problem is the created part(ie. timestamp) is displayed like this:

20040420214302

I am actually displaying the records using PHP. Is there a way of formatting the timestamp so that it's more readable?

Posted: Mon Apr 19, 2004 8:49 am
by kettle_drum
Change the mysql field to int(10) and you can then store the unix epoch time - number of seconds since 1970 - time(); - You can then call the value from the database and do many things with it using the pre-defined php functions.

Posted: Mon Apr 19, 2004 8:49 am
by JayBird
like this

Code: Select all

echo date("l dS of F Y h:i:s A", $timestamp);
Look here on how to format it to your exact needss - http://se.php.net/manual/en/function.date.php

Mark

Posted: Mon Apr 19, 2004 8:52 am
by JayBird
kettle_drum wrote:Change the mysql field to int(10) and you can then store the unix epoch time - number of seconds since 1970 - time(); - You can then call the value from the database and do many things with it using the pre-defined php functions.
...or set it to timestamp data type

Posted: Mon Apr 19, 2004 8:58 am
by liljester
dont forget the DATE_FORMAT function in mysql =)

Posted: Mon Apr 19, 2004 9:04 am
by JayBird
liljester wrote:dont forget the DATE_FORMAT function in mysql =)
You can't use DATE_FORMAT with timestamps as far as i am aware!?

Mark

Posted: Mon Apr 19, 2004 9:06 am
by magicrobotmonkey
or you can pull stuff with it using substr() - look it up at php.net

Posted: Mon Apr 19, 2004 9:11 am
by JayBird
magicrobotmonkey wrote:or you can pull stuff with it using substr() - look it up at php.net
What you mean :?

Mark

Posted: Mon Apr 19, 2004 9:16 am
by magicrobotmonkey

Code: Select all

<?php
 $year = substr("20040420214302", 0, 4);
 $month = substr("20040420214302", 4, 2);
 $day = substr("20040420214302", 6, 2);

 echo "$day/$month  $year";
?>
[/php_man]

Posted: Mon Apr 19, 2004 9:18 am
by JayBird
ah, ic.

The number he is using isn't a proper unix timestamp, just the years, month etc concenated together.

Mark

Posted: Mon Apr 19, 2004 9:22 am
by magicrobotmonkey
I know thats why if he wants to do it like that he has to manually extract data!

Posted: Mon Apr 19, 2004 10:11 am
by malcolmboston
Bech100 wrote:
liljester wrote:dont forget the DATE_FORMAT function in mysql =)
You can't use DATE_FORMAT with timestamps as far as i am aware!?

Mark
you can, this is how i use time in my sites

Posted: Mon Apr 19, 2004 10:16 am
by JayBird
depends what type of timestamp it is. You can only use it with a MySQL timestamp, not a UNIX timestamp.

Personally, i always use UNIX timestamps

Mark

Posted: Mon Apr 19, 2004 10:20 am
by malcolmboston
Bech100 wrote:depends what type of timestamp it is. You can only use it with a MySQL timestamp, not a UNIX timestamp.

Personally, i always use UNIX timestamps

Mark
yeah im talking about a MySQL timestamp, i never used to do it this way but i seem to of adopted it for quickness

Posted: Mon Apr 19, 2004 10:24 am
by JAM
liljester wrote:dont forget the DATE_FORMAT function in mysql =)
...or UNIX_TIMESTAMP
SELECT UNIX_TIMESTAMP(anydatefieldtype);
...then use PHP functions on it. Just a different approach...