SEO

#1 (**permalink**) 04-29-2008, 08:28 PM

Hi,

I'm currently studying for the CMDEV Dev-I exam and I'm a bit confused
about the actions of the TIMESTAMP data type.

If i create a table in MySQL 5 like so:

CREATE TABLE test1 (ts1 TIMESTAMP, i INT);

Then the timestamp column is automatically set to DEFAULT CURRENT
TIMESTAMP.

If I issue the insert

INSERT INTO test1 (ts1,i) values (NULL,1);

Then the ts1 (timestamp) column will store the current (insert)
timestamp. Which is right.

OK, so then I make another table,

CREATE TABLE test2 (ts1 TIMESTAMP NOT NULL DEFAULT '0000-00-00
00:00:00', i INT);

and issue the same query again on the new table:

INSERT INTO test2 (ts1,i) values (NULL,1);

The ts1 column STILL contains the current timestamp!

Am I missing something?

Thanks...

#2 (**permalink**) 04-29-2008, 08:28 PM

On 28 Apr, 02:56, macca <ptmcna...@googlemail.com> wrote:
> Hi,
>
> I'm currently studying for the CMDEV Dev-I exam and I'm a bit confused
> about the actions of the TIMESTAMP data type.
>
> If i create a table in MySQL 5 like so:
>
> CREATE TABLE test1 (ts1 TIMESTAMP, i INT);
>
> Then the timestamp column is automatically set to DEFAULT CURRENT
> TIMESTAMP.
>
> If I issue the insert
>
> INSERT INTO test1 (ts1,i) values (NULL,1);
>
> Then the ts1 (timestamp) column will store the current (insert)
> timestamp. Which is right.
>
> OK, so then I make another table,
>
> CREATE TABLE test2 (ts1 TIMESTAMP NOT NULL DEFAULT '0000-00-00
> 00:00:00', i INT);
>
> and issue the same query again on the new table:
>
> INSERT INTO test2 (ts1,i) values (NULL,1);
>
> The ts1 column STILL contains the current timestamp!
>
> Am I missing something?

The ability to read the manual?

#3 (**permalink**) 04-29-2008, 08:28 PM

> The ability to read the manual?

Wow! that was just SOOOO helpful!

If you can't say anything constructive, don't say anything at all.
Ass.

Sometimes it's not immediately apparent why something works the way it
does. I mean, if I insert NULL into the column it defaults to the
current timestamp even it I have set the default to '0000-00-00
00:00:00', but if i don't insert any data into the column and just
into the INT column, it defaults to 0000-00-00 00:00:00. I just
thought it was a bit strange.

#4 (**permalink**) 04-29-2008, 08:28 PM

On Mon, 28 Apr 2008 15:16:21 -0700 (PDT), macca wrote:
>
>> The ability to read the manual?
>
> Wow! that was just SOOOO helpful!
>
> If you can't say anything constructive, don't say anything at all.
> Ass.
>
> Sometimes it's not immediately apparent why something works the way it
> does. I mean, if I insert NULL into the column it defaults to the
> current timestamp even it I have set the default to '0000-00-00
> 00:00:00', but if i don't insert any data into the column and just
> into the INT column, it defaults to 0000-00-00 00:00:00. I just
> thought it was a bit strange.

Okay, http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

Third paragraph reads: The TIMESTAMP data type offers automatic
initialization and updating. You can choose whether to use these
properties and which column should have them:

followed by several paragraphs about how to use them. Note particularly
the part about the default value being a valid timestamp. "0000-00-00
00:00:00" is not a valid timestamp because there is no month zero nor
day zero. It is also not the special value ts 0, that turns off the auto
stuff.

--
Compared to system administration, being cursed forever is a step up.
-- Paul Tomko

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

TIMESTAMP Confusion