Go Back   SitePoint Forums > Forum Index > Program Your Site > PHP
Newsletter FAQ Members List Calendar Mark Forums Read

New to SitePoint Forums? Register here for free!

SitePoint Sponsor
 
Closed Thread
 
Thread Tools Display Modes
Old Jan 15, 2002, 16:43   #1
Caesar
SitePoint Guru
 
Caesar's Avatar
 
Join Date: May 2000
Location: On some harddisk
Posts: 661
Wink Timestamp(14) Question

Hi guys,

Let's say that I have a table with the following fields:

Fields: a (doesn't matter what kind of data type)
b (doesn't matter what kind of data type)
c timestamp(14)
d timestamp(14)

Everytime a new record is created, a and b fields are filled up together with the record created time in c.

As for d, I'll like to use a script that detects the time at which the script was run and have a timestamp(14) (in other words, "select now()+ 0" query) query run and placed into a certain unique record id (assume that a is unique).

Any ideas?
Caesar is offline  
Old Jan 15, 2002, 20:32   #2
freakysid
SitePoint Callithumpian
 
freakysid's Avatar
 
Join Date: Jun 2000
Location: Sydney, Australia
Posts: 3,845
There will be time, there will be time. Time for me and time for you...

You should be aware of the unique behaviour of the TIMESTAMP type. From the manual http://www.mysql.com/doc/D/A/DATETIME.html
Quote:
The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
You explicitly set the TIMESTAMP column to NULL.
TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

Let MySQL set the column when the row is created. This will initialise it to the current date and time.
When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value.
On the other hand, you may find it just as easy to use a DATETIME column that you initialise to NOW() when the row is created and leave alone for subsequent updates.

TIMESTAMP values may range from the beginning of 1970 to sometime in the year 2037, with a resolution of one second. Values are displayed as numbers.
So from that you might want to.
1) Swap the order of columns "c" and "d" if you are to use timestamp for both.

Also, your sql snippet "NOW() + 0" could be replaced with just "NOW()"
freakysid is offline  
Closed Thread

Bookmarks

« Previous Thread | Next Thread »

Thread Tools
Display Modes

 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Sponsored Links
 
Forum Jump


All times are GMT -7. The time now is 14:30.


Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Copyright 1998-2009, SitePoint Pty Ltd. All Rights Reserved