Formatting dates

abbey

hello everyone, this maybe an easy question for some. but how would i go about formatting a date and time from a mysql database.

What i have is a simple news script and everytime something is posted it adds a unix time stamp so it looks like this:

20020901021846

are there various ways of formatting this to look something like this:

09-01-2002

and the time something like this:

6:46 pm

any help would be greatly appreciated
thanks!

mrmufin

You could format the returned value in your PHP script:

echo date("m-d-Y h:i a",$timestamp);

Or you could have MySQL format the value as it pulls the record:

"SELECT FROM_UNIXTIME(timstamp_column,\"%m-%d-%Y %h:%i %p\") FROM table...";

Weedpacket

Problem of course is that that is not a Unix timestamp, but a MySQL-style timestamp - a number of the form YYYYMMDDHHMMSS. MySQL should have functions to convert this into a Unix timestamp for PHP to format, or format it into a date string itself.

abbey

hmm interesting, does anyone know those functions? i'm a bit new to the whole thing.

i'll look around while i see if anyone knows it.

thanks again

tony_l

If you are using MySQL- you can inser the Date and Time using the date and time functions that are built in to it such as the NOW() function.

Example:

$Query = "INSERT into table VALUES ('0',NOW(),'news item',);

In this example the query would insert the current Date and Time into the database where you have the 2cd field as a Date/Time datatype and the query has NOW() there without the single quotes.

MySQL allows you to format it in several ways -

yyyy-mm-dd

yyyy-mm-dd hh:mm:ss

and so on.

As for converting it to something like this:

mm-dd-yyyy

You would have to use the unix timestamp and insert it as a unix timestamp data type. When you pull from the DB you could convert it to the format that you want using what they suggested above- the time formatting functions.

Hope that cleared it up a bit- I know whne I first started out it took me forever to figure out how the heck the time functions worked with MySQl and especially PHP

abbey

the way i insert the time in the database is i use the curdate() function, now i'm not sure if thats the correct function to use, but its worked so far.

how would i go about inserting the time as a unix timestamp so i can covert it to other alternatives?

thanks for the help everyone, once i grasp the concept it should be easy from here on out 🙂 .

tony_l

Here is a VERY Useful Link in trying to figure out the date and time functions that are inherent in mySQL:

http://www.mysql.com/doc/en/Date_and_time_functions.html

(if that link no longer works- just navigate to the MYSQL site and do a search on date and time).

Ok, now as for how to insert a UnixTime Stamp, you can use the UNIX_TIMESTAMP() function like you would the NOW() function, but be sure that the date/time column that you are inserting to is set to the timestamp datatype.

Oh I just checked that link- it works- and i was wrong before- you CAN format a NOW() function in a differnt format using the PHP style formats like this-

'%D %y %a %d %m %b %j

I suggest looking at the link I gave because it has a lot of useful info on the formatting part.