[RFC] use date type DBMS field for type_date custom profile fields

Note: We are moving the topics of this forum and it will be deleted at some point

Publish your own request for comments/change or patches for the next version of phpBB. Discuss the contributions and proposals of others. Upcoming releases are 3.2/Rhea and 3.3.
Post Reply
Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

[RFC] use date type DBMS field for type_date custom profile fields

Post by Lucifer4o » Mon Jul 07, 2014 8:58 am

Hello all,

As I am in process of creating a PR for using Date type Data Base column for type_date custom profile filed, I was directed to put this RFC so we can discuss it.

State now:

Custom profile field type date uses for storage Varchar with length of 10 characters DB column. This allows board to record the date field in format DD-MM-YYY ( 1- 1-2014), where the leading zero to day and moth are skipped.

Custom Profile Field is validated using check for valid day, valid month and valid year, where valid year is every year after 1901.

Change:

I propose migrating to Data Base storage type Date for the custom profile fields (and user_birthday). This will not effect timestamp fields.

Why change:

As this is date, migrating to date type storage will give ability to search and filter users by date or period of time.

Proposed additional changes:

As the date type storage is with fixed length (no min and no max too). These custom profile filed values can be used as a way to control parser.
  • Allow entering dates without a year
    Allow admin the ability to require entering of year.
    Allow the filed in profile and miniprofile to show period elapsed in d/m/y
As the date type storage require valid date in format YYYY-MM-DD (2014-01-01) we can use the parser validation and make it not show only year if year is set outside of the phpBB valid year (1901) this way we keep the validity in front of the DBMS and allow flexability for the users.

Work Done:

1. Date type fields work with Date type date base column type.
2. "Always show now:" option work.
3. Date functional tests work with the new date string format.

PR:

https://github.com/phpbb/phpbb/pull/2689

User avatar
EXreaction
Registered User
Posts: 1555
Joined: Sat Sep 10, 2005 2:15 am

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by EXreaction » Sat Jul 12, 2014 5:51 pm

This may be a bad idea for DB compatibility. YYYY-MM-DD is the default for all AFAIK, but it may not necessarily be that way on the DB that the user is attempting to install phpBB.

I think we would at least want some functionality added to the DBAL for handling dates if we do something like this (to enforce YYYY-MM-DD in each different way each DB requires)

If we're only concerned about accuracy down to the day, we could instead use a numeric value as the number of days since a fixed point. We could cover 2,740 years with an unsigned integer of 6 digits. I'd assume the PHP date functions could handle this without errors by adding the number to a constant point in time

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by Lucifer4o » Thu Jul 24, 2014 12:36 pm

YYYY-MM-DD is the default supported format for dates for all DBMS-es supported by phpBB.

There is one more point in favour of date type field - there is support for date wothout year.

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by DavidIQ » Sat Jul 26, 2014 12:26 am

There is? I'm pretty sure if you don't specify a year in MSSQL you either get an error or it gets assigned the default of 1900.
Image

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by Lucifer4o » Wed Jul 30, 2014 7:08 am

I don't know on MSSQL, but on MySQL/MariaDB its 0000 when there is error.

The PR I've submited works with all DBMS-es currently supported by phpBB ...

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by DavidIQ » Thu Jul 31, 2014 1:20 pm

Lucifer4o wrote:I don't know on MSSQL, but on MySQL/MariaDB its 0000 when there is error.

The PR I've submited works with all DBMS-es currently supported by phpBB ...
Your two sentences contradict each other...

In any case if there is a PR then a developer will review it and will have to make the determination on if this will break other DBMS types since you're obviously not sure. From your statement you only know of this working with MySQL/MariaDB.

One thing to think about is that the culture of where the server on which the database is set up will determine what is a valid date so if for instance the culture of the server is set up as "de" or "es" etc. then 7/31/2014 will be invalid. This is probably one of the reasons that date column types are not used.
Image

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by Lucifer4o » Thu Jul 31, 2014 8:42 pm

No. The sentences are not contradictory to each other.

For the moment the date is saved as VARCHAR 10 in the format of DD-MM-YYYY regardless of the server local. The date type field format is YYYY-MM-DD.

The date is formed by PHP function create_date or make_date (I don't remember exactly).

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by DavidIQ » Fri Aug 01, 2014 2:42 pm

Right but because it's a varchar field and not a date field it doesn't really mater what the format is. Changing it to a date type will make it matter. As long as all DBMS types are ok with YYYY-MM-DD then it shouldn't be a problem.
Image

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: [RFC] use date type DBMS field for type_date custom profile fields

Post by Lucifer4o » Fri Aug 01, 2014 7:46 pm

They are. I have checked them all. This is the standard date format for all used DBMSs.


Post Reply