This topic contains 0 replies, has 1 voice, and was last updated by Mr. Mangus 8 years, 3 months ago.
Viewing 1 post (of 1 total)
Viewing 1 post (of 1 total)
You must be logged in to reply to this topic.
This topic contains 0 replies, has 1 voice, and was last updated by Mr. Mangus 8 years, 3 months ago.
In MySQL I needed to create table with two date fields.
One should serve as date entered, and one as date modified.
But you can’t have two fields od type TIMESTAMP in one table.
So, here is how I did a workaround:
CREATE TABLE tabContacts
( ID INT UNSIGNED NOT NULL auto_increment,
LastName VARCHAR(32) NOT NULL default '',
FirstName VARCHAR(32) NOT NULL default '',
Company VARCHAR(64) NOT NULL default '',
SearchType VARCHAR(16) NOT NULL default '',
ActivityCode VARCHAR(16) NOT NULL default '',
Status VARCHAR(16) NOT NULL default '',
AccountNum VARCHAR(32) NOT NULL default '',
DateEntered DATETIME DEFAULT CURRENT_TIMESTAMP,
DateModified TIMESTAMP,
KeyWords VARCHAR(64) NOT NULL default '',
MasterRemark VARCHAR(128) NOT NULL default '',
PrintForHim TINYINT NOT NULL default 1,
PrintForHer TINYINT NOT NULL default 0,
ModifiedBy VARCHAR(64) NOT NULL default '',
PRIMARY KEY (ID),
KEY (LastName, Company)
) CHARSET utf8;
As you can see, I designated one filed as DATETIME and the other as TIMESTAMP. To test how this works, I inserted a test record into this table:
INSERT INTO tabContacts(LastName, FirstName, Company, ModifiedBy)
VALUES ('Test','1234','2345','34567')
and the result was: DateEntered: 2016-01-08 14:39:27 DateModified: 0000-00-00 00:00:00
Now I need to test an update. Will update auto-update DateModified field? Here is an update statement:
UPDATE tabContacts SET LastName = ‘TestUpdate’
WHERE msLastName = ‘Test’
Let’ look at DateEntered and DateModified. Nothing changed. Both fields stayed the same:
DateEntered: 2016-01-08 14:39:27 DateModified: 0000-00-00 00:00:00
You must be logged in to reply to this topic.
Posted | Modified |
---|---|
Nov 16 | Dec 2 - 14:52 |
2017-11 - From the Editor - Who is My President?... | |
Nov 29 | Nov 26 - 14:21 |
Sat *WARREN* 09:00 - - - Sun *BROWN* 09:00... | |
Nov 26 | Nov 26 - 14:19 |
19 Game Results - 2017-11-26 SU - Diego dropped others - Andres scored 4... | |
Nov 24 | Nov 24 - 18:10 |
Adventures in the Dominican... | |
Oct 11 | Nov 17 - 20:32 |
Home... |
Rani S | Nov 15 |
Nice lesson, thank you for sharing with us :)... | |
Scottgal | Nov 4 |
Всем привет, уже третий день не могу поставить D... | |
useaglefcu.org | Jul 4 |
In MongoDB, is it possible to update the value of ... | |
Andre | Jun 24 |
Works with Server 2016 IIS 10.... |
|
Copyright © 2024
Domain Web Center . All Rights Reserved. Magazine Basic theme designed by Themes by bavotasan.com . Disclaimer | Powered by WordPress and bbPress. | Contact the Editor |