Hi list,

i tried to create a table with inserted & lastupdated timestamp fields:

create table temp (
id int not null primary ke auto_increment,
data varchar(100),
inserted timestamp default now(),
lastupdated timestamp(8));


how do i get mysql to put in the current timestamp for inserted &
lastupdated fields when i insert a record and only lastupdated when i
update the record?

thanks,
T. Hiep

I would use a trigger (at least for the update)....

The first insert should work with now() and you can leave lastupdateted
empty


Olaf


On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:

> Hi list,
>
> i tried to create a table with inserted & lastupdated timestamp fields:
>
> create table temp (
> id int not null primary ke auto_increment,
> data varchar(100),
> inserted timestamp default now(),
> lastupdated timestamp(8));
>
>
> how do i get mysql to put in the current timestamp for inserted &
> lastupdated fields when i insert a record and only lastupdated when i
> update the record?
>
> thanks,
> T. Hiep
>

Triggers are a fine idea, but I would use a trigger for both cases..
no point putting that level of housekeeping on the application when
you can set rules in the database and more or less forget about it.

- michael


On 9/4/07, Olaf Stein <steino@ccri.net> wrote:
> I would use a trigger (at least for the update)....
>
> The first insert should work with now() and you can leave lastupdateted
> empty
>
>
> Olaf
>
>
> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:
>
> > Hi list,
> >
> > i tried to create a table with inserted & lastupdated timestamp fields:
> >
> > create table temp (
> > id int not null primary ke auto_increment,
> > data varchar(100),
> > inserted timestamp default now(),
> > lastupdated timestamp(8));
> >
> >
> > how do i get mysql to put in the current timestamp for inserted &
> > lastupdated fields when i insert a record and only lastupdated when i
> > update the record?
> >
> > thanks,
> > T. Hiep
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

Agreed...
Also for consistency's sake


On 9/4/07 3:15 PM, "Michael Dykman" <mdykman@gmail.com> wrote:

> Triggers are a fine idea, but I would use a trigger for both cases..
> no point putting that level of housekeeping on the application when
> you can set rules in the database and more or less forget about it.
>
> - michael
>
>
> On 9/4/07, Olaf Stein <steino@ccri.net> wrote:
>> I would use a trigger (at least for the update)....
>>
>> The first insert should work with now() and you can leave lastupdateted
>> empty
>>
>>
>> Olaf
>>
>>
>> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:
>>
>>> Hi list,
>>>
>>> i tried to create a table with inserted & lastupdated timestamp fields:
>>>
>>> create table temp (
>>> id int not null primary ke auto_increment,
>>> data varchar(100),
>>> inserted timestamp default now(),
>>> lastupdated timestamp(8));
>>>
>>>
>>> how do i get mysql to put in the current timestamp for inserted &
>>> lastupdated fields when i insert a record and only lastupdated when i
>>> update the record?
>>>
>>> thanks,
>>> T. Hiep
>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>
>>
>

is it possible to do without trigger?

i google and found this link:
http://sql-info.de/mysql/examples/CR...-examples.html

but when i tried to combine two examples into one CREATE statement and it
didn't work.

any idea?

is there a way to create this table that accomplishes these two goals?

thanks,
T. Hiep

On Tue, 4 Sep 2007, Michael Dykman wrote:

> Triggers are a fine idea, but I would use a trigger for both cases..
> no point putting that level of housekeeping on the application when
> you can set rules in the database and more or less forget about it.
>
> - michael
>
>
> On 9/4/07, Olaf Stein <steino@ccri.net> wrote:
>> I would use a trigger (at least for the update)....
>>
>> The first insert should work with now() and you can leave lastupdateted
>> empty
>>
>>
>> Olaf
>>
>>
>> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:
>>
>>> Hi list,
>>>
>>> i tried to create a table with inserted & lastupdated timestamp fields:
>>>
>>> create table temp (
>>> id int not null primary ke auto_increment,
>>> data varchar(100),
>>> inserted timestamp default now(),
>>> lastupdated timestamp(8));
>>>
>>>
>>> how do i get mysql to put in the current timestamp for inserted &
>>> lastupdated fields when i insert a record and only lastupdated when i
>>> update the record?
>>>
>>> thanks,
>>> T. Hiep
>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>
>>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> - All models are wrong. Some models are useful.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=hiep@ee.ucr.edu
>
>

There is nothing terribly wrong with the approach documented in
'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
you no doubt have read, it does mean that you have to make sure that
every insert statement is specifically designed to set the *second*
timestamp field to now() and then count on the built-in properties to
see the first one updated on every UPDATE.

The only other caveats are:
your application behaviour is now dependent on the ordering of
columns; ok in the short-term, increasingly annoying over time as
maintainence phases grow the app in complexity.
importing data from your system to another system might prove
hairy as you figure out how to temporarily avoid this bevahiour to
keep your data intact.

The trigger method is universal in that this solution will port to any
half-way reasonable database engine....

but, as in all things IT, do whatever best meets your circumstances.

- michael

On 9/4/07, Hiep Nguyen <hiep@ee.ucr.edu> wrote:
> is it possible to do without trigger?
>
> i google and found this link:
> http://sql-info.de/mysql/examples/CR...-examples.html
>
> but when i tried to combine two examples into one CREATE statement and it
> didn't work.
>
> any idea?
>
> is there a way to create this table that accomplishes these two goals?
>
> thanks,
> T. Hiep
>
> On Tue, 4 Sep 2007, Michael Dykman wrote:
>
> > Triggers are a fine idea, but I would use a trigger for both cases..
> > no point putting that level of housekeeping on the application when
> > you can set rules in the database and more or less forget about it.
> >
> > - michael
> >
> >
> > On 9/4/07, Olaf Stein <steino@ccri.net> wrote:
> >> I would use a trigger (at least for the update)....
> >>
> >> The first insert should work with now() and you can leave lastupdateted
> >> empty
> >>
> >>
> >> Olaf
> >>
> >>
> >> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:
> >>
> >>> Hi list,
> >>>
> >>> i tried to create a table with inserted & lastupdated timestamp fields:
> >>>
> >>> create table temp (
> >>> id int not null primary ke auto_increment,
> >>> data varchar(100),
> >>> inserted timestamp default now(),
> >>> lastupdated timestamp(8));
> >>>
> >>>
> >>> how do i get mysql to put in the current timestamp for inserted &
> >>> lastupdated fields when i insert a record and only lastupdated when i
> >>> update the record?
> >>>
> >>> thanks,
> >>> T. Hiep
> >>>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
> >>
> >>
> >
> >
> > --
> > - michael dykman
> > - mdykman@gmail.com
> >
> > - All models are wrong. Some models are useful.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=hiep@ee.ucr.edu
> >
> >
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

Just do this...

create table temp (
id int not null primary key auto_increment,
data varchar(100),
inserted timestamp default 0,
lastupdated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

And just use

Insert into temp (inserted ) values (NOW());

You're only inserting once, so just do it in the code.

Then no need for triggers -- what a waste. Mysql will update the lastupdated
field.

Note the 'default 0' that is important... When you have multiple timestamp
columns and want one to be auto handled.

D.Vin

> -----Original Message-----
> From: Hiep Nguyen [mailto:hiep@ee.ucr.edu]
> Sent: Tuesday, September 04, 2007 12:02 PM
> To: mysql@lists.mysql.com
> Subject: timestamp for update and insert
>
> Hi list,
>
> i tried to create a table with inserted & lastupdated
> timestamp fields:
>
> create table temp (
> id int not null primary ke auto_increment,
> data varchar(100),
> inserted timestamp default now(),
> lastupdated timestamp(8));
>
>
> how do i get mysql to put in the current timestamp for inserted &
> lastupdated fields when i insert a record and only lastupdated when i
> update the record?
>
> thanks,
> T. Hiep
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>
>

so, if trigger is used then

create table temp (
id int not null primary key auto_increment,
data varchar(100),
inserted timestamp,
lastupdated timestamp)

is good enough, right? trigger will use now() function to set inserted &
lastupdated.

any thought on backup & restore tables & tringgers???

thank you for your s.
T. Hiep

On Tue, 4 Sep 2007, Michael Dykman wrote:

> There is nothing terribly wrong with the approach documented in
> 'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
> you no doubt have read, it does mean that you have to make sure that
> every insert statement is specifically designed to set the *second*
> timestamp field to now() and then count on the built-in properties to
> see the first one updated on every UPDATE.
>
> The only other caveats are:
> your application behaviour is now dependent on the ordering of
> columns; ok in the short-term, increasingly annoying over time as
> maintainence phases grow the app in complexity.
> importing data from your system to another system might prove
> hairy as you figure out how to temporarily avoid this bevahiour to
> keep your data intact.
>
> The trigger method is universal in that this solution will port to any
> half-way reasonable database engine....
>
> but, as in all things IT, do whatever best meets your circumstances.
>
> - michael
>
> On 9/4/07, Hiep Nguyen <hiep@ee.ucr.edu> wrote:
>> is it possible to do without trigger?
>>
>> i google and found this link:
>> http://sql-info.de/mysql/examples/CR...-examples.html
>>
>> but when i tried to combine two examples into one CREATE statement and it
>> didn't work.
>>
>> any idea?
>>
>> is there a way to create this table that accomplishes these two goals?
>>
>> thanks,
>> T. Hiep
>>
>> On Tue, 4 Sep 2007, Michael Dykman wrote:
>>
>>> Triggers are a fine idea, but I would use a trigger for both cases..
>>> no point putting that level of housekeeping on the application when
>>> you can set rules in the database and more or less forget about it.
>>>
>>> - michael
>>>
>>>
>>> On 9/4/07, Olaf Stein <steino@ccri.net> wrote:
>>>> I would use a trigger (at least for the update)....
>>>>
>>>> The first insert should work with now() and you can leave lastupdateted
>>>> empty
>>>>
>>>>
>>>> Olaf
>>>>
>>>>
>>>> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:
>>>>
>>>>> Hi list,
>>>>>
>>>>> i tried to create a table with inserted & lastupdated timestamp fields:
>>>>>
>>>>> create table temp (
>>>>> id int not null primary ke auto_increment,
>>>>> data varchar(100),
>>>>> inserted timestamp default now(),
>>>>> lastupdated timestamp(8));
>>>>>
>>>>>
>>>>> how do i get mysql to put in the current timestamp for inserted &
>>>>> lastupdated fields when i insert a record and only lastupdated when i
>>>>> update the record?
>>>>>
>>>>> thanks,
>>>>> T. Hiep
>>>>>
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>>
>>>>
>>>
>>>
>>> --
>>> - michael dykman
>>> - mdykman@gmail.com
>>>
>>> - All models are wrong. Some models are useful.
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=hiep@ee.ucr.edu
>>>
>>>
>>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> - All models are wrong. Some models are useful.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=hiep@ee.ucr.edu
>
>

If you decide to use the trigger here is the syntax

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

And that table structure looks ok to me


As far as the backup goes just dump the mysql database, which you should be
doing anyway to backup users etc

Olaf



On 9/4/07 3:59 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:

> so, if trigger is used then
>
> create table temp (
> id int not null primary key auto_increment,
> data varchar(100),
> inserted timestamp,
> lastupdated timestamp)
>
> is good enough, right? trigger will use now() function to set inserted &
> lastupdated.
>
> any thought on backup & restore tables & tringgers???
>
> thank you for your s.
> T. Hiep
>
> On Tue, 4 Sep 2007, Michael Dykman wrote:
>
>> There is nothing terribly wrong with the approach documented in
>> 'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
>> you no doubt have read, it does mean that you have to make sure that
>> every insert statement is specifically designed to set the *second*
>> timestamp field to now() and then count on the built-in properties to
>> see the first one updated on every UPDATE.
>>
>> The only other caveats are:
>> your application behaviour is now dependent on the ordering of
>> columns; ok in the short-term, increasingly annoying over time as
>> maintainence phases grow the app in complexity.
>> importing data from your system to another system might prove
>> hairy as you figure out how to temporarily avoid this bevahiour to
>> keep your data intact.
>>
>> The trigger method is universal in that this solution will port to any
>> half-way reasonable database engine....
>>
>> but, as in all things IT, do whatever best meets your circumstances.
>>
>> - michael
>>
>> On 9/4/07, Hiep Nguyen <hiep@ee.ucr.edu> wrote:
>>> is it possible to do without trigger?
>>>
>>> i google and found this link:
>>> http://sql-info.de/mysql/examples/CR...-examples.html
>>>
>>> but when i tried to combine two examples into one CREATE statement and it
>>> didn't work.
>>>
>>> any idea?
>>>
>>> is there a way to create this table that accomplishes these two goals?
>>>
>>> thanks,
>>> T. Hiep
>>>
>>> On Tue, 4 Sep 2007, Michael Dykman wrote:
>>>
>>>> Triggers are a fine idea, but I would use a trigger for both cases..
>>>> no point putting that level of housekeeping on the application when
>>>> you can set rules in the database and more or less forget about it.
>>>>
>>>> - michael
>>>>
>>>>
>>>> On 9/4/07, Olaf Stein <steino@ccri.net> wrote:
>>>>> I would use a trigger (at least for the update)....
>>>>>
>>>>> The first insert should work with now() and you can leave lastupdateted
>>>>> empty
>>>>>
>>>>>
>>>>> Olaf
>>>>>
>>>>>
>>>>> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@ee.ucr.edu> wrote:
>>>>>
>>>>>> Hi list,
>>>>>>
>>>>>> i tried to create a table with inserted & lastupdated timestamp fields:
>>>>>>
>>>>>> create table temp (
>>>>>> id int not null primary ke auto_increment,
>>>>>> data varchar(100),
>>>>>> inserted timestamp default now(),
>>>>>> lastupdated timestamp(8));
>>>>>>
>>>>>>
>>>>>> how do i get mysql to put in the current timestamp for inserted &
>>>>>> lastupdated fields when i insert a record and only lastupdated when i
>>>>>> update the record?
>>>>>>
>>>>>> thanks,
>>>>>> T. Hiep
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> MySQL General Mailing List
>>>>> For list archives: http://lists.mysql.com/mysql
>>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> - michael dykman
>>>> - mdykman@gmail.com
>>>>
>>>> - All models are wrong. Some models are useful.
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=hiep@ee.ucr.edu
>>>>
>>>>
>>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> - All models are wrong. Some models are useful.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=hiep@ee.ucr.edu
>>
>>