Date and Time in PHP and MySQL

TheBeginner

Hi,

I would like to insert a date and time in my table when I put in information. I would like to have date and time, so I guess that DATETIME or TIMESTAMP(10) is preferred to use in the database.

My question is how to insert the date and time into the database with php?
How can I obtain the information of time and date with php?
I don't really know how to get the same format of date and time in both php and MySQL so that it is possible to insert it into a database?

GilesGuthrie

One of the most reliable ways is to use the UNIX timestamp. If you write:

$now = date("U")

Then $now is set to the number of seconds since UNIX began, which is a sizeable number. I think you'll need a BIGINT to store it.

Then, when you retrieve the timestamp from your DB, you fire it at the date() function, sending the formatting arguments in the string, and your retrieved timestamp after, so...

$insertedtime = msyql_result(mysql_query("SELECT timestamp FROM table WHERE 1"), 0, "timestamp");
echo date("dS F Y", $insertedtime);

The joy of this is that you can apply time zone modifiers to the time stamp by enclosing the maths inside another set of brackets. I.e. if your server is in the eastern US, and your reader is in the UK, you want to add 5 hours, so you would do:

echo date("dS F Y", ($insertedtime + (5 * 60 * 60)));

See here for more info on the date() function..

TheBeginner

Thanks. That was a good tip!

So what you actually store in the database is BIGINT ?

What is then the purpose to have a option to use TIMESTAMP in MySQL ? It is implemented so why not use that. Maybe the TIMESTAMP can be updated automatically when I insert a new row? Do you know that?

GilesGuthrie

Originally posted by TheBeginner
Thanks. That was a good tip!

So what you actually store in the database is BIGINT ?

What is then the purpose to have a option to use TIMESTAMP in MySQL ? It is implemented so why not use that. Maybe the TIMESTAMP can be updated automatically when I insert a new row? Do you know that?

Sorry, the field type in the database is BIGINT, this will ensure that enough space is reserved to store the timestamp, which is, as I said, quite a large number.

The UNIX timestamp just acts as a solid reference point, and it's an integer, so it's really useful if you need to perform operations on the time, as I showed in the example.

Let me know if I didn't answer the question - it's late, it's been a long day yada yada yada.

TheBeginner

You DID answer my question. I will try it and see how it works!

TheBeginner

But I will still be looking fopr another solution. Because when I'm looking directly into the database with phpadmin I would like to se the date and time directly and not only a big number that has no meaning to me. Just for convenience.....

chadu

instead of using "U" when you format the date (which gives you the Unix Epoch timestamp) try looking at the date() function entry in the PHP manual on how to format dates in many convenient ways.

Thora_Fan

If you want to have a timestamp automatically entered in when you use INSERT via MySQL, your best and easiest bet is to make the field NOT NULL. For instance,

CREATE TABLE whatever (
....
myTimeStamp TIMESTAMP NOT NULL);

This way, whenever you insert a new row into that table, it will automatically add the timestamp and you don't have to worry about it.

As far as reading timestamps, read them backwards. Here's a timestamp from a table value from a table I have.

20030318014624

If you read it like yyyy/mm/dd/hh/mm/ss if makes sense. For instance,

2003/03/18/01/46/24

Hope this helps.