TIMESTAMP data type auto-fill problem what am I missing?

[kmartin]

This seams like a very useful tool. Have a table with a column of the data type TIMESTAMP, and auto fill that field in a form with the unique timestamp of date+time. I’m having a bad time with this little gremlin. Much is due to my newness to LibreOffice. I have searched the web: found https://www.youtube.com/watch?v=OBtxupBSN6A
and this forums: Insert the current date and current time into a new database record? .

The tools > SQL > command to execute >

//fill in transaction timestamp field

ALTER TABLE “DailySales” ALTER COLUMN “transaction” SET DEFAULT CURRENT_TIMESTAMP

executes but I get no auto fill in the form. In fact I can not save the form until I enter something in the field and then the data in the table is bad, e.g. 1800-01-01 01:03:00 .

I am using Version: 5.2.5.1

So here I am…
and thanks in advance

martin

[EasyTrieve]

re: “get no auto fill in the form”, Yes, this is kind of klunky. Worse is that if you move to a new record (save the record ), and then go back to it it shows “01/01/1800” which is wrong (both wrong data and the extra 00 at the end). Only when I close the database and re-open does it appear correctly as “02/28/17”. First idea is to mark these in a special color like purple to remind you that they are funky. Hope to write some Basic someday to improve user experience.

[Ratslinger]

There are a number of issues with dates, table and View defaults. Here are a couple: Bug 102945 and Bug 104375.

This doesn’t mean you cannot use a timestamp. With the set-up you stated, change the control data property on the form to Input required = No:

Do this for both the Date and Time controls (or columns if using a grid). Do NOT use any defaults in the controls themselves. Now when entering a new record, you can leave the field blank for a current timestamp or enter your own information if an override is wanted. When the record is written to the table, if the field(s) are blank on input. the current timestamp will be entered by the database.

In the case of defaulting to a CURRENT_TIMESTAMP, nothing is displayed because it is not known exactly when the record will be written to the table.

If you ALWAYS want the current timestamp entered (never an override), you can even eliminate the fields (columns) from the entry form completely. The DB will then always use the current timestamp on a new record.