Tricky Date Problem!

9 posts by 2 authors in: Forums > CMS Builder
Last Post: March 30, 2016   (RSS)

By Perchpole - March 29, 2016

Hello, All

I'm adding a feature to a site run by a charity. They want to add a filterable list of all the former senior members - along with details of when they died. The charity was formed at the end of the 19th century so some of the dates go a long way back. The problem is that some of the data is so old that the dates are incomplete. Most have only a month and a year. A typical example would be: 08/1872 or 05/1900.

Dates from the last 30 years are as expected, e.g: 12/07/1986, etc.

The question is, how can I clean up the data so that I can compare and filter the dates?

I can't use strtotime() on a date without a day!

For filtering/comparison operations, where no day is given, it would be acceptable to assume the person died on the 1st of the given month.

Any help would be much appreciated.

:0/

Perch

By Perchpole - March 30, 2016

Hi, Damon -

Thanks for your support.

All of the dates are already in the database. There are over 1,500 of them. New dates are added via a proper date gizmo - so will be in the proper format.

I need to work with what I've got if possible.

:0/

Perch

By Damon - March 30, 2016 - edited: March 30, 2016

Using the MySQL Console that is included in the Developer Console plugin, what does that date format look like for dates without days?
http://www.interactivetools.com/add-ons/developer-console/

A date entering using the date dropdowns or date picker looks like this:
2016-03-29 12:02:49

Depending on what the date format looks like for dates without the days entered, you may be able to do a search and replace with a MySQL statement to add the day.

Let me know what the date format in the database looks like for dates without days entered.

Cheers,
Damon Edis - interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - March 30, 2016

Hi, Damon –

The dates in the DB have been entered as simple strings:

  • 04/67
  • 10/71
  • 09/73
  • 13/04/82
  • Etc…

Perch

By Damon - March 30, 2016

Hi,

If you leave the simple strings dates as is and then enter records with new dates using the date picker or dropdown date field, the formats will be different and you will not be able to sort on them.

You need to convert dates in this format:

04/67
10/71
09/73
13/04/82

to be in a date format like this:

1967-04-01 00:00:00
1971-10-01 00:00:00
1973-09-01 00:00:00
1982-04-13 00:00:00

Then when the dates are all in the same format you can sort them and output them in the format needed.

How to do this is the question. You could export the table with the dates to CSV and then work on them in a plain text/code editor.

After the changes, save and import the data back into CMS Builder.

Will this work for you?

Cheers,
Damon Edis - interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - March 30, 2016

Hi, Damon -

I had thought of that approach but it's not really a solution.

As I said in my original post, for ordering and filtering it would be ok to infer that a person died on the first of the month. However, if I actually change the date then I am confirming that as a fact - which I cannot do.

What I was hoping to find was a way of pulling the dates out of the DB and tricking the ordering system into treating this 04/1967 as if it was actually 01/04/1967.

This would enable me to order the entire list like this:

23/03/1967

29/03/1967

04/1967

02/04/1967

12/04/1967

05/1967

03/05/1967

Etc.

:0/

Perch

By Damon - March 30, 2016

Hi,

If you changed the date format to be like this YYYY/MM/DD, you could use the sorting in CMS Builder and the frontend:

1967/03/27
1967/04
1967/04/02
1967/05

This would also work with dates that don't have a day entered. Maybe moving forward instead of having a date picker which will give you another format, you could just have a text field with instructions after it to use this format: YYYY/MM/DD

You can also limit the allowed characters to be numbers and forward slash.

Sorry, I'm running out ideas. 

Cheers,
Damon Edis - interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Perchpole - March 30, 2016

Thanks. I appreciate your help.

:0)