Insert & Update Timestamps

Complement ….
Finally I found a solution with the attribute “default current_timestamp on update current_timestamp” on an hidden Timestamp field.
The problem is that it works ONLY if the field is displayed on the creation form (insert only). If the field is hidden, the timestamp is not set while on the update form, the timestamp is set even if the field is hidden.
I qlso tried to put CURRENT_TIMESTAMP as default value on the field in Designer, but still no effect if field is hidden on creation form.

• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.

Hi @sturmy31,

To store a time stamp for on insert you can add a separate column as:
default insert_timestamp on insert current_timestamp

But you must hide this column in your data entry form. Otherwise it will be overwritten.

The disadvantage is that you have the insert and update timestamps in separate columns, but at least you have them both. ??

Does this help?
Peter

Hi Peter,
Thanks for your QUICK answer which solves my problem.
Thank you again for your great job !
Roger

Great! ?? Good luck Roger

Hi Peter,
Sorry, but after testing it doesn’t work ??
In fact, MySQL doesn’t permit to have more than 1 field of type timestamp (or Datetime) with DEFAULT or “on update” CURRENT_TIMESTAMP in the same table.
Some workaround are discussed here : https://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla
BUT … these solutions are based on Not Null timestamp fields with which you can set to 0 and then have CURRENT_TIMESTAMP set instead.
Having Not Null Timestamp field we can’t hide them on WP DAta Access Forms …

Even if I define a template Entry with nullable fields and after set these fields to not null without reconciliate, it doesn’t work (message : “Item CretationDate must be entered”).

I would like to have all DDL defined into WP Data Access, thus no trigger.

Is there a possibility to “force” a not null field to be hidden, and then in that case, to set the field to its current value or to Default for an insert ?

Thank you
Roger

• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.

Hi Roger,

What happens if you add these columns:

...
,date_inserted datetime default current_timestamp
,date_updated datetime default current_timestamp on update current_timestamp
...

This works for me!

Let me know,
Peter

Hi Peter,

Doesn’t work:
create table test_cur_timestamp (date_inserted datetime default current_timestamp
,date_updated datetime default current_timestamp on update current_timestamp)
==> #1067 – Valeur par défaut invalide pour ‘date_inserted’

create table test_cur_timestamp (date_inserted datetime default ‘0000-00-00 00:00:00’,
date_updated datetime default current_timestamp on update current_timestamp);
==> #1067 – Valeur par défaut invalide pour ‘date_updated’

create table test_cur_timestamp (date_inserted timestamp default current_timestamp
,date_updated datetime default current_timestamp on update current_timestamp)
==> #1067 – Valeur par défaut invalide pour ‘date_updated’

create table test_cur_timestamp (date_inserted timestamp default ‘0000-00-00 00:00:00’
,date_updated timestamp default current_timestamp on update current_timestamp)
==> Works ! … but fields must be mandatory

create table test_cur_timestamp (date_inserted timestamp default current_timestamp
,date_updated timestamp default current_timestamp on update current_timestamp)
==> #1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

In fact I think you have no problem because you’re using a recent version of MariaDb.
I use Version 5.5.42 !
In forum they say that this problem is corrected in newer versions.
I have no the possibility to migrate…

It would be great to be abble to hide mandatory fields in “Entry” if there is a Default Value defined !
Regards
Roger

It would be cool to have $$NOW$$ variable to put as Default Value even if the value is set at the load of the page containing form and not when the form is posted.
??
Kind regards
Roger

Hi Roger,

It looks like your DBMS version does not support your requirements. Can you update? I tested on MariaDB 10.5 and MySQL 8.0. It works on both.

Anyway, the $$NOW$$ is a great feature request! ?? I love it! I’m actually preparing the next update. I’ll try to add the default value. For the logic I need some more time. I’ll try to add it the update after this one.

Thank you for your great feature request! ??

Best regards,
Peter

Hi Peter,
No need to migrate except for this problem, so I’ll may be wait $$NOW$$ !
Don’t forget the possibility to hide mandatory fields in Entry if they have default value defined.
If it’s complicated to see that on table, we can imagine to specify it on the Entry template using $$KEEPACTUALVALUE$$ which means you put something like that:
UPDATE MyTable SET MyTable.MandatoryField = MyTable.MandatoryField
Best regards
Roger

• This reply was modified 3 years, 6 months ago by sturmy31.
• This reply was modified 3 years, 6 months ago by sturmy31.

Hi Roger,

I added two new variables you can use to add a default date|time:
$$NOW$$ = current date
$$NOWDT$$ = current date + time

I cannot change the program flow just before a new release. That’s too risky. The flow has a number of rules and exceptions and chances are that changes lead to unexpected errors for other users. This has to be tested first. We can pick this up with the next update.

But you can add your own logic using the wpda_after_simple_form hook, see:
https://wpdataaccess.com/2020/01/29/action-hook-wpda-after-simple-form-to-add-custom-elements-to-end-of-data-entry-forms/

As a temporary solution you could add your own javascript code to hide these items. Let me know if you need help with it! ??

Best regards,
Peter

Hi Péter,
Thanks a lot!
I’ll wait the next version with $$NOW$$.
When can we expect this new version ?
Kind regards
Roger

Hi Roger,

The latest update was just released this morning and contains your defaults. A small section was added to the documentation:
https://wpdataaccess.com/docs/documentation/project-templates/data-entry/#supported-default-values

Hope this helps,
Peter

Hi Peter,
Wonderful !
Thanks a lot !
Roger