storing date mysql

[rohitrohitrohit]

hi all,
Donn't know much php...........
i want that when user register,his registration date store in db.i want to use server date not his system date in db.

thankx in adva...

[pritaeas]

php already uses the server's date.

[Designer_101]

i want that when user register,his registration date store in db.i want to use server date not his system date in db.

Php uses the server date, unlike other languages it is not reliant on the users system date and time.
Do you need help coding this or was it just a question?

[rohitrohitrohit]

actually i have a table user_register_mobile in mysql. [Email,MobileNumber,Registratinon_date... are some filed there.] in this table i have a field registration_date.i want to keep date so that i know when user register to our site.

i read about timestamp,some date,time functon of php,but didn't get it. but if u want explain it i am ready to learn

[Designer_101]

In your mysql table you should set the type to 'timestamp' and then set the default to 'CURRENT TIMESTAMP'.

It doesnt really need to be explained. All that is doing is getting the date and time from the server and sticking it in a field. Just remember not to update it when you update the table, that way it will stay the day the user registrated.
Hope this helps, if you need anything else or i've miss understood dont hesitate to ask.

Also, if you need more info on timestamp and storing the date, i suggest you consult the manual. It contains some really usefull stuff.

[mschroeder]

Store a UNIX Timestamp in that mysql column.

<?php
$iCurrentTime = time();

$iCurrentTime would result in an integer like: 1236884436
Which is the number of seconds since the Unix Epoch.

You can pass that integer into the php date() function and format it any way you want, and you can also calculate against it simply using basic math.

The only drawback is that it can not store dates after 01/2038 because it will exceed the size of a 32bit integer or Before 01/1970. By 2038 I imagine 64bit hardware or greater will pretty much be the norm.

[almostbob]

likewise sql now()
is equivalent to php time()

[mschroeder]

or UNIX_TIMESTAMP()

[rohitrohitrohit]

i did as u told me in last thred,i insert a recode but when i see column Registration_date it shows 0000-00-00 00:00:00?

[mschroeder]

That is because your column type is set to datetime or timestamp not certain which one actually produces that as I don't use the mysql datetime or timestamp data type.

To store a unix timestamp you just need an INT(10)

[Designer_101]

what do you mean when you view it?

All this is is when new set of data is inserted into the my_sql table. It stores that exact time and date?
Try inserting a new set of data and see what it displays?

[digital-ether]

That is because your column type is set to datetime or timestamp not certain which one actually produces that as I don't use the mysql datetime or timestamp data type.

To store a unix timestamp you just need an INT(10)

It really is best to store a mysql timestamp, or datetime as mentioned:

In your mysql table you should set the type to 'timestamp' and then set the default to 'CURRENT TIMESTAMP'. 

It doesnt really need to be explained. All that is doing is getting the date and time from the server and sticking it in a field. Just remember not to update it when you update the table, that way it will stay the day the user registrated. 
Hope this helps, if you need anything else or i've miss understood dont hesitate to ask. 

Also, if you need more info on timestamp and storing the date, i suggest you consult the manual. It contains some really usefull stuff.

So just create a column of type timestamp. And set the default to 'CURRENT TIMESTAMP'. Can't be any more simple.

This will default to the mysql timezone, however you can change this per connection or globally. http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

When you retrieve the date, you can format it directly on mysql or pass it to PHP to do the formatting.

To format it in MySQL:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

To use PHP to format, first convert it to a UNIX_TIMESTAMP.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp

ie:

"SELECT *, UNIX_TIMESTAMP(Registration_date) AS timestamp FROM your_db_table_name LIMIT 1"

You can then use PHP's date formatting functions to format the timestamp.

Using this method provides more portability of the database data, as well as reduces errors with timezones.

The main reason to use mysqls built is dates is that you can query based on the dates, which are indexed, and thus very fast as well as use the mysql date functions in your queries.

[Designer_101]

Using this method provides more portability of the database data, as well as reduces errors with timezones.

The main reason to use mysqls built is dates is that you can query based on the dates, which are indexed, and thus very fast as well as use the mysql date functions in your queries.

Thats a really good point. Also php and mysql don't use the system date like I said above. This way YOU have control. Also like digital-either said, it gets rid of many problems with timezones.

Hope all this helps