[Solved] Alter Table T1 Alter Column "Date" set default

Creating tables and queries

[Solved] Alter Table T1 Alter Column "Date" set default

Postby F3K Total » Fri May 10, 2013 7:43 pm

Hello,
i wonder, if there is a possibility for setting another timestamp by default then
Code: Select all   Expand viewCollapse view
CURRENT_TIMESTAMP
?
e.g.
Code: Select all   Expand viewCollapse view
Alter Table T1 Alter Column "Date" set default CURRENT_TIMESTAMP - 1 DAY

I know, this does not work.
I'm using a splitted or a server Version of HSQL 2.2.8, so also Triggers are possible, but i don't know if neccessary and when, how to do it.
Can anybody help me?
And a further question is, if it would be possible to decide between different default values e.g. on the current time.
If it's before 12:00 AM then CURRENT_TIMESTAMP - 1 DAY
If it's after 12:00 AM then CURRENT_TIMESTAMP + 1 DAY
Rik
Last edited by F3K Total on Sun May 12, 2013 12:48 am, edited 1 time in total.
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 913
Joined: Fri Dec 16, 2011 8:20 pm

Re: Alter Table T1 Alter Column "Date" set default

Postby Villeroy » Fri May 10, 2013 8:29 pm

HSQL 1.8 can not calculate any time stamps except for the current time stamp.

I tried with HSQL 2.2.8 which has a DateAdd function:
Code: Select all   Expand viewCollapse view
ALTER TABLE "T1" ALTER COLUMN "Date" SET DEFAULT DATEADD('day',-1,CURRENT_TIMESTAMP)

but it does not accept any function name after keyword DEFAULT.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26868
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alter Table T1 Alter Column "Date" set default

Postby F3K Total » Sat May 11, 2013 8:27 am

Good morning,
thank you for your reply Villeroy.
I'm not used to TRIGGERS but with following code, made from snippets, I'm close to a solution, 'cause it inserts the desired Date once, before the error cardinality violation
occurs.
Code: Select all   Expand viewCollapse view
CREATE TRIGGER DAYBEFORETRIG AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
    SELECT DATEADD('dd',-1,CURRENT_DATE) INTO "Date" from "Testtable";
END

Any ideas?
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 913
Joined: Fri Dec 16, 2011 8:20 pm

Re: Alter Table T1 Alter Column "Date" set default

Postby Sliderule » Sat May 11, 2013 4:14 pm

Rik:

You said / asked:

Rik wrote:If it's before 12:00 AM then CURRENT_TIMESTAMP - 1 DAY
If it's after 12:00 AM then CURRENT_TIMESTAMP + 1 DAY

The above, makes no sense, at least, not to me. Since, 12:00 AM is MIDNIGHT ( put another way . . . 00:00:00 ) . . . it is NOT possible for it to be "before" that time.

I mention this, because, unless we can better understand exactly your needs, it is hard to provide a response. Perhaps, you can give several examples, to show us exactly what you want the database back-end to perform, so, someone might be able to help with a solutions.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: Alter Table T1 Alter Column "Date" set default

Postby F3K Total » Sat May 11, 2013 6:46 pm

Hello Sliderule,
I'm sorry, if we would use the 24 hour time, i mean 12 o'clock. Isn't it high noon?
The if-else question is secondary, what i first would like to do, is to set another timestamp/date then Current_Timestamp/Current_Date as default.
But if possible, i'd like to have a solution for this question:
Somebody wants to to set a Date as Work-beginning Date.
They have a DB to store datas of Phonecalls. If e.g. the first call is stored, with default current_timestamp, at 0.00 AM to 6.00 AM, it means, that the Guy startet working at 11:00 PM the day before. Now they like to have the Date of the day before as Work-beginning Date.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 913
Joined: Fri Dec 16, 2011 8:20 pm

Re: Alter Table T1 Alter Column "Date" set default

Postby Sliderule » Sat May 11, 2013 10:23 pm

While I think I know what you want . . . I must say, I am NOT 100% certain.

Let me tell you what I did . . . I 'tested' this using HSQL 2.2.8 as my database backend.

I created a 'test' table as follows:

Code: Select all   Expand viewCollapse view
CREATE CACHED TABLE "Testtable" (
   ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
   TEXT VARCHAR_IGNORECASE(50),
   "Date" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   VALUE DECIMAL(10,2) DEFAULT 0.00);


I created a TRIGGER as below:

Code: Select all   Expand viewCollapse view
CREATE TRIGGER DAYBEFORETRIG
AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
   IF HOUR(NEWROW."Date") <= 11 THEN
      UPDATE "Testtable" SET "Date" = DATEADD('dd', -1, "Date") WHERE ID = NEWROW.ID;
   END IF;
END


Explanation 1:
  1. The TRIGGER is executed, AFTER the INSERT to the table. The reason for this is, I think you may want to 'adjust' the value for the field "Date" . . . depending on the time of day.
  2. The value for the table field "Date" ( really a timestamp ) only is changed to the prior calendar day ( really an UPDATE ), when, HOUR(NEWROW."Date") is less than or equal to 11 ( 11 o'clock in the morning . . . so . . . until 11:59:59 ).
In my 'testing', with the following INSERT statements . . . AND . . . run on my computer with a computer date/time of 2013-05-11 10:23:15' ( notice this is BEFORE 12:00 NOON :) ):

Code: Select all   Expand viewCollapse view
INSERT INTO "Testtable" (TEXT, "Date", VALUE) VALUES('Hello World', CURRENT_TIMESTAMP, 20.2);
INSERT INTO "Testtable" (TEXT, VALUE) VALUES('Hello World', 120.2);
INSERT INTO "Testtable" (TEXT, "Date", VALUE) VALUES('Hello World', {TS '2013-05-15 15:23:00'}, 320.2);


The resulting values were:

Code: Select all   Expand viewCollapse view
ID  TEXT         Date                      VALUE
--  -----------  -----------------------  ------
1   Hello World  2013-05-10 10:23:15.00    20.20
2   Hello World  2013-05-10 10:23:15.00   120.20
3   Hello World  2013-05-15 15:23:00.0    320.20

Explanation 2: Notice how, the DATE was changed to the prior date ( NOT an input date of 2013-05-11, but rather, 2013-05-10 ), when the TIME was BEFORE 12:00 NOON . . . however, when the TIME was AFTER 12:00 NOON, the date was NOT changed ( the third record inserted, with a HARD-CODED date ). So, you can 'modify' the UPDATE statement in the TRIGGER, to meet your specific requirements ( use of PRIMARY KEY to identify ( database pun intended ) the row just INSERTED ). :)

Now, I am hoping this will give you a place to start, so you can 'fine-tune' the TRIGGER to you exact needs.

Please be sure to let me /us know if this helps, and, best of luck.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: Alter Table T1 Alter Column "Date" set default

Postby F3K Total » Sun May 12, 2013 12:09 am

Hello Sliderule,
Sliderule wrote:While I think I know what you want . . . I must say, I am NOT 100% certain
You did get me!
This trigger does exactly what i wanted to have! Thank you very much.
Now it's possible for me to insert any date/timestamp depending on current values and other conditions into a DB.
e.g. a different start/end of a period, depending on someones age, sex or anything else.
Thanks again.
Rik
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 913
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Alter Table T1 Alter Column "Date" set default

Postby F3K Total » Sun May 12, 2013 9:54 pm

Hello Sliderule,

i hope you don't mind if i add an extended question here.
The great trigger that you gave me, works on inserting a new row.
I now added one Column to the table, so that i have on column "CurrentDate" containing the current Timestamp and a second one "SpecialDate" containing the modified timestamp, like this:
Code: Select all   Expand viewCollapse view
CREATE CACHED TABLE "Testtable" (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
   TEXT VARCHAR_IGNORECASE(50),
   "CurrentDate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,"SpecialDate" TIMESTAMP,
   VALUE DECIMAL(10,2) DEFAULT 0.00);

and applied this trigger for testing
Code: Select all   Expand viewCollapse view
CREATE TRIGGER DAYBEFORETRIG
AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
   IF HOUR(NEWROW."CurrentDate") <= 1 THEN
      UPDATE "Testtable" SET "SpecialDate" = DATEADD('dd', -1, "CurrentDate") WHERE ID = NEWROW.ID;
   ELSE
      UPDATE "Testtable" SET "SpecialDate" = DATEADD('dd', -2, "CurrentDate") WHERE ID = NEWROW.ID;
   END IF;
END
which is working very good on inserting a new row.

What i now would like to try is, if i change the timestamp (in "CurrentDate") manualy on an already exiting row, how does a second trigger on the same Table has to look like, that it updates "SpecialDate" Row in the same manner as the first trigger does?
I've tried it by changing your trigger from:
Code: Select all   Expand viewCollapse view
AFTER INSERT ON "Testtable"
REFERENCING NEW ROW AS NEWROW...

to
Code: Select all   Expand viewCollapse view
AFTER UPDATE ON "Testtable"
REFERENCING OLD ROW AS OLDROW ...

But it doesn't work.
Can you help me once again?
Rik
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 913
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Alter Table T1 Alter Column "Date" set default

Postby Sliderule » Sun May 12, 2013 10:46 pm

Rik:

OK, how about trying the following BEFORE UPDATE Trigger:

Code: Select all   Expand viewCollapse view
CREATE TRIGGER DAYBEFORETRIGUPDATE
BEFORE UPDATE ON "Testtable"
REFERENCING OLD ROW AS OLDROW NEW ROW AS NEWROW

FOR EACH ROW WHEN (OLDROW."CurrentDate" <> NEWROW."CurrentDate")
    BEGIN ATOMIC
    IF HOUR(NEWROW."CurrentDate") <= 1 THEN
       SET NEWROW."SpecialDate" = DATEADD('dd', -1, NEWROW."CurrentDate");
    ELSE
       SET NEWROW."SpecialDate" = DATEADD('dd', -2, NEWROW."CurrentDate");
    END IF;
END

Explanation 1: What the above does, is:

  1. Reset the calculated value for NEWROW."SpecialDate" . . . BUT . . . only when OLDROW."CurrentDate" <> NEWROW."CurrentDate" . . . <> means . . . NOT EQUAL TO . . . put another way, only when a change in the value of "CurrentDate" .

  2. Notice, that, since this is a BEFORE UPDATE statement . . . you do NOT perform an actual UPDATE, since, HSQL ( your database back-end ) will do it, automatically, BUT with the modified value for "SpecialDate", exactly as you defined it ( at least I think ).
Explanation 2: You may use the following Select statement, to 'document' the Triggers you have in place.

Code: Select all   Expand viewCollapse view
Select
   TRIGGER_NAME,
   ACTION_TIMING,
   EVENT_MANIPULATION,
   EVENT_OBJECT_TABLE,
   ACTION_ORDER,
   ACTION_CONDITION,
   ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS;

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Alter Table T1 Alter Column "Date" set default

Postby F3K Total » Sun May 12, 2013 11:10 pm

Hello Sliderule,
wonderfull! The trigger works exactly as it should. Now it's not only possible for me to change datas on inserting new records, but also on updating existing records.
Sliderule wrote:Select
TRIGGER_NAME,
ACTION_TIMING,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_ORDER,
ACTION_CONDITION,
ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS;

This is also a wonderfull help not to loose control over the triggers, or even better, i thought before, if that would be possible?
I'm near to say, with my small experience: These triggers are better then lots of macros, changing the DB'S data from outside.
I'll keep them save in mind and try to use them as often as possible.
Great Job!!!
Thank you again, Sliderule
Rik
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 913
Joined: Fri Dec 16, 2011 8:20 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests