Jay Blanchard wrote:
> The issue with timestamp is this:
> You can only have one timestamp with a default of the current date.
> My app has two fields in one table
> Created_date datetime default now()
> Last_update datetime default now()
> This doesn't work with timestamp because timestamp doesn't support two
> columns with default current_timestamp. Additionally, it looks like if
> default to current_timestamp then any time the record is modified, that
> value is changed.
> Well, George, you never mentioned that this was your problem. And you
> would run into the same problem, given your definition above, regardless
> of database (unless the database product has a hack to account for it, I
> am not aware of any).
Not true. PostgreSQL can do it. If you want the timestamp modified every
time, a record is changed, you can use triggers to achieve this
transparently. In PostgreSQL you can also set the default value (at
creation time) to the output of a function.
> But it is simply fixed. When creating the row you
> include as your value for Created_date, NOW()....
> INSERT INTO tblFoo (Created_date) VALUES (NOW())
> ...and then you never modify the Created_date again. The column with the
> timestamp will continue to update properly when the record is UPDATED.
My understanding was the timestamp fields were only set when the record
is created. They are not changed when the record is modified.
> While tinker-toys were wildly popular (I had them), they are wholly
> for large scale building projects.
> I disagree. As shown by this link, a computer desk has been made from
> tinker toys. http://www.charm.net/~jriley/tinkertoy.html :)
> As far as MySQL is concerned it has been documented that there are more
> than several large scale database application being utilized today,
> including projects at Fortune 500 companies.
Indeed, but it depends on your application. If you are running something
big but very simple (e.g. 1 daily batch if INSERTs over night, and the
rest of the day of millions of SELECTs), MySQL is fine. On any project
where I actually have to manipulate the data and do more complex things,
I have been finding that MySQL simply isn't up to it.
> I personally manage
> several MySQL databases containing 100's of millions of records on
> OpenBSD and Linux systems. I have seen examples of MySQL databases
> larger than the ones I am intimately familiar with. There are several
> folks on this list who operate MySQL databases for large scale projects.
Size isn't the problem here. Functionality for dealing with such
databases in a clean way is.
Horses for courses, as ever. If MySQL isn't capable enough for your
application, the correct solution is to find a more suitable database -
not moaning about how MySQL isn't good enough for your specific
application, just because you are afraid of learning how to use
something slightly different.