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...