Easiest way to store time and date onto MySQL with PHP?

tickled_pink

I need to store the time and date that a user registered with a site. What's the easiest way of doing it in PHP? The resulting info needs to be then stored in a MySQL table.

Also, now that I think about it, the client is based in the UK but his present server is located in the US. As all their clients are going to be based in the UK, all time and datestamps should be in UK date format and showing GMT time for when they registered.

stolzyboy

have you looked at the [man]datetime[/man]

tickled_pink

Yes. The difficulty I'm having is understanding how to store that information into MySQL's date/time fields. Or is it just easier to store the information into a text field?

Hiccup

To save massive amounts of extra coding, just store the date as an integer in your mysql table. Then use php's date commands to manipulate it as you please.

Sure mysql has pretty ways to store the data as date or time fields, but why bother? It is 100% easier to store the date/time as an integer. Just use $time=time() and mktime() functions.

I've done it both ways and letting php handle the date/times is the most efficient way.

majiclab

I find that storing the time/date in the database is the best way. Then you can use MySQL's nice little functions like DATE_FORMAT and what not.

If all you are doing is storing when the account was created, just use a simple DATETIME field, and in the query, do this:

INSERT INTO table ... VALUES (..., UNIX_TIMESTAMP())

The UNIX_TIMESTAMP() function will insert the UNIX timestamp according to GMT...

That should solve all of your problems!

-Percy

drawmack

create a timestamp field and let mysql populate the field itself.

majiclab

The only problem with using a TIMESTAMP field is that if you don't UPDATE it with the old TIMESTAMP, then it gets re-written to the UDPATE time, instead of staying at the original INSERT time.

If you want to use TIMESTAMP, make sure you UPDATE it with the original value, otherwise it will always be changing on you when the user changes their profile.

-Percy

G_Otalora

Or you can use the MySQL DATETIME data type.