Which MySQL date field do you use?

sglane

This could be for birth dates, last update date, creation date, etc.

eoghain

In case you don't get it from the results. Always use TIMESTAMP. It's the most versital of the fields, and even though the others have different names they are still timestamps to MySQL.

xblue

I would not really agree. The reason why there are different formats to my understanding is that they are used for different purposes. Timestamp columns auto-update on insert or update operations with the current date and time, and I don't think this would be the desired behaviour if you e.g. store address information and birth date and a user updates his address.

Sure, you could set the timestamp column to its previous value like

UPDATE table SET address='$new_address', birthdate=birthdate

and I think it should work, but I'd always use a date column type here so I don't have to bother with that column in my update statement at all.

eoghain

You are correct about the auto update, but that isn't for all databases, as far as I know only MySQL does that and it's only the first timestamp field in the table.

In general a timestamp field (which DATETIME in MySQL qualifies) is useful in more situations than a simple date. But you are correct it depends on the situation that you are working with. I tend to use the timestamp fields for many situations even when just a date would suffice because you never know what someone is going to want in the future, and if you are already gathering the information then you have less re-work to do.

3dron

If I may bud in, I was wondering which to date column type to use myself.

I want a date to only be set to the current on 'insert.' I don't want it to be touched on 'update' unless i give it a value.

And I don't really want to 'cache' the value before update and then resend it.

I has an earlier thread asking how to use now(). Never got a response though.

thanks for any help.

PHPdev

I prefer using INT to store the Unix Timestamp... Seems to work easier with PHP for met at least

PHPdev

3dron

Ok, so if I use 'int' how do I assign the timestamp at sign up.

A hidden value, with what value?

Please explain.

PHPdev

3dron,

First I have to assume certain things...

On signup you mean when a new user fills out a form or other data on your site, and you want to record the time and date they want to fill out a form

PHP has a built in function time() which returns a Unix Timestamp. Now I have never used this on a Windows box, but by the definition of the function time() it should still return a Unix Timestamp...

So all you need to do is in you insert or update statement for MySQL use time()

Example Insert:
$sql = "INSERT into members (first_name, last_name, subscription_date) values ('Walley','Cleaver', " . time() . ")";

Example Update:
UPDATE members set sub_date = " . time()

I also beleive there is a MySQL function UNIX_TIMESTAMP which will also insert a Unix Timestamp

Hope this helps... Let me know if you have any other questions...

PHPdev

3dron

I think it makes sense. SO I can put a hidden form value in my form for the date that equals time().

S0 do I make it equal:
<input type="hidden" name="date" value="time()" size="32">

I don't quite understand the "." (perios) in the statment.

Can the column type be a date or datetime field? Does the time() function put the date also?

Thanks

Ron

PHPdev

Ron,

I am going to try to clarify the time() function, so please bear with me.

The TIME() function is a PHP funciton so if you are to use it in HTML then you will need to enclose it with the <?php ?> tags. These tags tell the PHP processor (I use the term processor loosely) to examine and execute the code between them.

So if you want to use the TIME() in HTML you can do it like:

you can find out more at http://www.php.net/time

UNIX TIMESTAMPS: Unix and Linux use time as the number of seconds since the Unix Epoch (Which I beleive is like Dec 17 1969). Because it uses seconds most Timestamps are an integer on the order of 1,XXX,XXX,XXX Another number good to know is there are 86,400 seconds in a day. Because the Unix TImestamp is an integer it makes it easy to add or subtract amounts of time from a stored time. NOTE: There is not a date_add or date_subtract funciton in PHP. Also because the Unix Timestamp stores seconds it not only stored the Seconds, Min, Hour, but also the Month, Day Year. Another function you may want to look at is date(), http://www.php.net/date this function will take a Unix Timestamp and put it in a format you define.

Back to your form...

One thing you may want to consider about using a hidden form element is that it is going to store the time the form was loaded, not the time the form was submited. If you use this function in your insert or update statement for the database then you will more acuratly (Probably with in a few thousanths to hundreths of a second) get the time the user actually submitted the form.

On to the "." operator.

PHP uses the "." between strings to concatinate the two strings together. Meaning if I have

String1: How are you today
String2: Im doing fine

In PHP I can do: "How are you today" . "Im doing fine";
or if String1 and String2 are variables: $String1 . $String2;

I use this operator a lot to dynamicly build my SQL statements, and peice together parts of an HTML page

I hope this will give you a little better understanding of what I was thinking before.

Let me know if you have any other questions...

PHPdev