Refactoring user search to allow search/filter by custom fields

[Lucifer4o]

Hmmm I have a question regarding dates in SQL DBs other then MySQL/Maria DB.

As I started to work on the date field, the date is kept in D-M-YYYY format. In MySQL/Maria DB - this is valid request:

SELECT * FROM phpbb_users
WHERE user_birthday > '30- 7-1984'
ORDER BY phpbb_users.user_birthday ASC

Will this be a valid request in other SQL databese (MS SQL, Postgre) ?

If not I will have to request a change of the dateformat on other ticket.

[DavidIQ]

Most, if not all DBMSs actually follow or allow/understand the format YYYY-MM-DD

[Lucifer4o]

This will not do the trick ... that missing 0 on the lower then 10 days and months ... Can't we just change the datefield to accept timestamp?

*Nevermind the first part ... I had mistake in my request ... but still a valid qestion - can't we change the format of the date field?

[DavidIQ]

What benefit would there be in doing that?

[Lucifer4o]

It will allow searchability in custom date fields.

Easear sort ... and evrywhere else there is used a timestamp. Only in custom date fields (including birthday) it is used with this date statement. And the field is not in date type, but a VARCHAR.

[DavidIQ]

Ah yes...was thinking that the field was VARCHAR but thought something else was going on now in 3.1 since the comparison still worked. Now I see that the database seems smart enough to be able to do a conversion. If it gets changed to another type it would have to be integer (timestamp) like all other date/time fields plus a conversion would be needed, which may not work very well in all cases.

[Lucifer4o]

I can write a migration script ...

Something along the lines of:

Get all field names for type date
For each of them do the following:
Change name to some hash (so we don't duplicate it)
Create new column with type int.
Start converting old dates to unix timestamp and write them to the new column
Drop hashed column (old one)
Go to next ...

Should I implement it with this ticket, or should I create a new one?

This will take a long time but ... it can be done. What is the conversion limit? 100, 250, 500 rows at a time?

I think I can have it working to tomorrow morning. I'll just need some help with migration part - where should I put the code exactly.

[EXreaction]

1960-01-01

Just try to make a UNIX timestamp for it.

[Lucifer4o]

Rofl ... that is a fair point ...

But you only get negative timestamp.

[DavidIQ]

Some forums have modified the collection of this data as well so they only collect certain portions of the date, I.e. only the year, or month and year, or month and day, etc. Since this is a VARCHAR field the data then gets stored strangely in some cases: "19-12- " or " - -1990" so on so forth making a conversion a bit more difficult.