[SOLVED] Automatic deletion of data from the database after a defined time.

Good morning, everyone,

I have a small problem with my database on my site.
Indeed, the principle is simple:

  • A user wishes to reserve a SAUNA for a certain time, he enters his NAME (Michel for example), the SAUNA NUMBER (012) and the DURATION (15 minutes). (No problems here)
  • He clicks on the button to send the data to the database. (No problems here)
  • After 15 minutes, the previously entered data (Michel, 012, 15) are automatically deleted from the database. (This is where it blocks)

I do not know how to proceed so that at the end of the elapsed time according to which was selected, the data are automatically deleted from the database.

Is that possible? What are the manipulations to do?

Thanks in advance!

Hi,

there are a few ways to proceed here, expiration time column in the db and a filter on the queries or post read hook, you also need to synch calendars forward etc.
however those aspects are all quite complex to code, and i strongly recommend you first check out Wix booking before you proceed

there is already an initial integration of booking with wix code and we are working on exposing additional features of booking through code.

please let me know if it will work for you,
Shlomi

Hello,

Thanks for your answer, unfortunately I can’t use wix booking because it’s not what i’m searching, here’s a really quick video I made for some explanations :

Hi Jean,

thank you for taking the time to create the video, however i am missing the wider context of what you are trying to do and the technical choices behind the current design of the database, and maybe i could suggested another way to implement this feature
in some databases you have ttl (time to live) for a record as a feature, a capability we do not support that at the moment in wix code. behind the scenes the data stays in the db but with an additional column with the time in which it is expired, and on read query filter only those records which are not expired.
you can also create such a filter for your read queries, and implement this paradigm on insert of a new record into your database

Shlomi

Hi,

By doing my researches on the ttl thing (not conclusive), I was thinking about the hooks we can make. Can I use a hook in my database to check what value is in the “TIME” column and then make a timer with the javascript’s conditions to determinate an automatic deletion of the case ?

DB hooks execute before or after a db operation, so if it suits your scenario you can use them, such as before any read of the collection. It will however slow down the reads as it will require additional db hits

Yup, my scenario is that the DB look what value have been added to it (10 in my video) and execute a script that delete the case that contain the value after 10 minutes.

I am doing something similar to a time to live on my site with temporary roles. I have it set a date X minutes in the future and when the data is read if it is past that time remove the role.

When writing to the database add
targetDate = new Date()
targetDate.setDate(targetDate.getMinutes()+ Number_of_minutes )

save targetDate in a Date/Time spot in the dataset

you could have a query hook on the dataset look through the data and if new Date() > DatasetValue have it delete the item, of have a script dedicated to doing that in your backend and have it be called.

you can always pass {“suppressHooks”: true } as the WixDataOptions to skip the check if it is not needed that query to speed up the result

@shlomsh I think his scenario uses scripts that automatically run in the background without external input. Like the Automation except they run code instead of sending an email and there trigger is a Timer event. Personally I would love being able to do this.

Hi jean,
Indeed i recommend you follow the timestamp example like Etan also suggested, create a time to live end date for the record.
Two additional notes:
My first recommendation is to just disregard a record if it is no longer active and therefore available for other users to use, it will save the need to create and remove records.
Any backend code is shortly lived and only effective for the current request execution, creating a timer or any other delayed execution is not guaranteed to run once a response has been delivered for the main request or a timeout for the entire request has passed.
A timer for a callback execution every time interval can be done using an external call to http function using services out there like zappier, ifttt etc, however going this way the current use case is not the right way to go :slight_smile:

Good evening !

Thanks for your answers. Concerning the time of reservation and the ttl value it works (thx you both), now the last thing to be done is the code about the check “if actual_time > ttl => delete the line” thing.