Simple time function

[bobnew32]

Ok, I have a script that inserts the current datetime into the mysql database as 2003-09-12 17:31:47 which is find with me. I need to know how to edit that string of information ie: $date= $row['date'] that will edit sytax of $date to change it to this syntax to this: F j S, Y . I just cant find the right string formater! Please help!

[bobnew32]

This is really easy to do, can I please have some help? I tried looking for tutorials, but they all say how to modify the current date().

[dmorison]

You need to go via strtotime [uk.php.net].

Something like:

$timeval = strtotime($row['date']);

$newdate = date("F j S, Y",$timeval);

echo $newdate;

You may want to consider storing the PHP time() value in your database as an INT(11) instead of using mysql's built in datetime format.

[killroy]

Might bne more efficient to use the SQL function DATE_FORMAT in the query. MySQL is devilishly optimized.

SN

[bobnew32]

Ok that works perfectly now I need to input the data of the current time in the form 0000-00-00 00:00:00 into the database. I have a form to handle it, but would the variable that contains the 0000-00-00 00:00:00 be $date= date()? What would I use?

[jamie]

hi bob,

you don't have to input a current time at all. why not use a timestamp field [mysql.com] in your database which automatically inserts the current time everytime you insert or update the row in question. when selecting this field from your database, if, for example, your timestamp field is called 'date' use

select x, y, z, UNIX_TIMESTAMP(date) as date from table_x where bla = bla

this returns your timestamp field as a unix timestamp which you can then format in any way you like using the date() [nl.php.net] function.

this has the advantage that

a) you never have to bother inserting the current time as mysql does it automatically every time you insert or update a row

b) you retrieve the field as a unix timestamp which is easily formateable using date() without the strtotime step

have a read of the links above, everything you could possible need is there.

good luck

[jatar_k]

I stay away from timestamp cols all together and just use DATe cols and insert NOW() or SYSDATE() that takes care of getting the proper format in the field. As long as the date col has the proper format then you're laughing.

I even tend to split date cols and time cols a lot of the time if I use the values seperately and it save s some processing.

The problem I have with timestamp cols is that if you alter the table or have a function that affects every row then all your timestamps get changed. I have had probs with this a few times in the past.

There are also tricks like having two timestamp cols and mysql will only ever update the first one and the second stays protected. This does work for date created and last modified setup but I think an alter table still changes them both, I cant quite remember.

[bobnew32]

Ok, my script inputs the NOW() with php into the mysql timestamp. Then I do this code where $date is selected from the database normally as this type 20030914164442 . But then the date on the page says this when I run the script! : December 31st, 1969

$date= $row['date'];
$timeval = strtotime($date);

$newdate = date("F jS, Y",$timeval);

[bobnew32]

I think I described the problem thouroughly enough, if I need to reword it just say so. >_<

[RussellC]

IMHO, the fastest most efficient way to format dates out of a MySQL database is to do the date formatting in the SELECT statement like this:

SELECT DATE_FORMAT(datefield, '%M %D, %Y') as date FROM table WHERE ...

I just converted a program I wrote to do all date formatting like this and it sped it up way more than I expected.

Also, make sure your feild type is DATETIME.