How to manage dates on an auto refreshing demo site

For this website, I have just built a public demo for the web app (with restricted functionality and a shared public login etc) so you can test drive the service before signing up.

At the moment it resets the data every 60mins by truncating all the relevant tables and then reading in SQL statements from a file, which repopulates the database with the fresh test data again. This is to delete all the test data from users and restore all the deletions etc, tidying up all the playing that users will do during the demo.

So far so good and all working fine.

However, a lot of the test data includes dates, like due dates and start dates etc. Over time these dates will all become in the past and the restored data will be restoring data that now looks messy. So a new user logging into the demo system will see loads of alerts about overdue stuff etc. The calendar will be blank on the current month because all the events will be in the past. All the trackers and reminders will be alerted as 'overdue' as well etc etc.

So my question is:
If I am resetting the data by reading in each sql insert statement, one line at a time, and running the query from that line of text, how can I adjust any dates? Is there a way to do an insert statement that has a relative date, like say, make this date two weeks after today? And even if there is, I generate the SQL file by exporting my tables in an SQL file. A simple and quick procedure. I create all the data on the system, export the file and use that to restore the data on the demo site. Without changing the database (ie adding fields for relative from today for all the dates) how can I make these dates relative to today and keep my simple procedure for generating the test data?

The system will change and develop over time, so as it is now, I just replicate the database, upload the sql file, and away the demo goes again all fresh and up to date. So I need to be able to do this 'date' thing without changing the database tables.

Does anyone have any suggestions or ideas about how I could approach getting these dates to stay current? Or do I have to just regularly reset my Sql file with new data. (Which is a pain as there is so much of it).

I hope that all makes sense,

Thanks in advance for any help,

Best wishes,

Paul.

When you create your database for the fresh install periodically, why not use php's function to get today's date then use this as a bench mark to set all your other dates?

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

I thought about trying that, but here is the problem:

An example of the SQL file being read in line by line:

Code:

INSERT INTO `planner` VALUES
(1, 3, 2, 'Case #0003', '2016-01-12', NULL, '2016-01-14', NULL, NULL, 3, 0, 0, 0, 'case'),
(2, 3, 2, 'Final Confirmation', '2015-12-17', NULL, NULL, NULL, NULL, 3, 0, 0, 0, NULL),
(3, 3, 2, 'Meeting with agencies', '2015-11-11', NULL, NULL, NULL, NULL, 6, 0, 0, 0, NULL),
(4, 3, 2, 'Deadline for applicants', '2016-01-06', NULL, NULL, NULL, NULL, 0, 0, 3, 0, NULL),
(5, 3, 2, 'Deadline for Interviews', '2016-02-09', NULL, NULL, NULL, NULL, 0, 0, 3, 0, NULL),
(6, 3, 2, 'Office Party', '2015-10-14', NULL, NULL, NULL, NULL, 0, 1, 0, 0, 'standalone'),
(7, 3, 2, 'Away Day', '2015-11-19', NULL, NULL, NULL, NULL, 0, 1, 0, 0, 'standalone'),
(8, 3, 2, 'Exec Visits', '2015-12-08', NULL, NULL, NULL, NULL, 0, 1, 0, 0, 'standalone'),
(9, 3, 2, 'Accounts Due', '2016-03-08', NULL, NULL, NULL, NULL, 0, 1, 0, 0, 'standalone');

So on this table, the date is the fifth entry, and is of date format.

On this table

Code:

INSERT INTO `last_viewed` VALUES
(3, 0, 19, '2015-10-16 15:03:10'),
(3, 47, 0, '2015-10-16 23:45:11'),
(3, 0, 56, '2015-10-16 23:48:47'),
(3, 66, 0, '2015-10-16 23:48:48'),
(3, 0, 45, '2015-10-17 01:05:29'),
(3, 116, 0, '2015-10-17 01:10:14'),
(3, 29, 0, '2015-10-17 01:10:43');

On this table it is the fourth, and a timestamp format.

So if I tried to get the line, and do a find and replace, how would I do that? How would I pick out the dates to be replaced from the string, and replace each one with a date that equaled that time period.

What I am trying to avoid doing is having to manually create all the data on the system (which took about 2 days to do) in order to have current dates, in order to export is as an sql file, for the refresh data procedure to read.

Hmmm. I am really stuck on this. I just had not considered the dates going out of date (so to speak) when I planned how to do this. I wonder what other demo systems do when it comes to dates?

Thanks for taking the time to read my lengthy post though, much appreciated.

Paul.

I'm not entirely sure why you have two different time formats, shouldn't they all be the same for consistency.

You can still use php to get just the date part of a date time string, and add say a set number of days or months, so I don't see this as being an issue of having to do a search and replace which would be bad practice anyway.

E.g.

http://stackoverflow.com/questions/37276...ate-in-php

Also doing it in mysql...

http://stackoverflow.com/questions/12862...rmat-value

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

The dates are different because on one table I needed the time as well as date, and on another table I needed just the date.

The problem I think, and it is helping me work this out just by explaining it to someone, is that I am getting the insert SQL form a string read in from a file. So I have a string, that somewhere in it has a date, that might be of date format or date-time format. I would have to search through the string to pick out the dates, do whatever php conversion to the date I need, then insert it back into the string. The carry on searching for the next date format in that string etc etc.

However other tables might have date formats in them that should not be converted.

It seems like it is going to be a nightmare any way I approach this.

Paul.

Hmm, if that is the case then it sounds like an initial bad planning phase for your data( please don't take that the wrong way.)

Whilst it isn't too difficult to select dates from a string or text file using regex, it might have been better to put a place holder in there instead.

Almost like how codeigniter uses templates or json e.g

"some string containing text and {"date":2015-10-10","databaseid":234"} some more text"

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

I think that once a month I am going to have to manually open my SQL file and do a text replace on the dates.

That seems the easiest option to me at the moment.

I think this is a case of bad planning on my part (or perhaps just an unforeseen problem I couldn't solve).

Thanks for taking the time though.

If you did want to write a crude string parser using the json example I gave the following is a good starting point. Good luck!

http://stackoverflow.com/questions/21994...n-a-string

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

Here is another alternative... using just regex to find dates

http://stackoverflow.com/questions/19564...any-string
http://stackoverflow.com/questions/37732...-01-123545

Hope you found that useful!

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

I am not sure if I have understand your need but why do you not use MYSQL expressions for generating the dates in the interval you need them? > NOW() + - days, hours, minutes and etc..

Best VPS Hosting : Digital Ocean