Use SQL Timestamp & Datetime for dates in 4.0

[KT Walrus]

Please consider changing the database schema to using TIMESTAMP (or DATETIME) for dates instead of INT(10) in 4.0.

[bfarber]

What benefits do you see that bringing, out of curiosity?

[KT Walrus]

What benefits do you see that bringing, out of curiosity?

The benefits aren't huge, I admit. But, it would make queries that are relative to a datetime or timestamp easier to express in SQL (e.g., show posts that are 1 month old, or people born on a Sunday). MySQL has a pretty complete set of functions to manipulate timestamps (

But, I have to admit that my primary motivation for requesting this change is that debugging would be easier for me (either through using phpmyadmin to browse rows in tables or just var_dump'ing out PHP variables that have timestamps in them).

Finally, my programming preference is to use appropriate data types (when available) to represent internal data. In fact, I'd like to see the use of ENUMs more in the IPB schema (which is about the same thing as using DATETIME and TIMESTAMP types for times).

[Shariq Ansari]

I agree with all of the above, except for the ENUMS, simply because I find them limiting and usually think normalization is preferable, as what people assume are static enum values often change...

Having MySQL's (or any database's) built-in date functionality work more seamlessly seems like it could improve overall code quality as well.

Heck, I'd like to see views, triggers, and stored procedures used, too :)

[Guest]

I agree on the front of using DATE/DATETIME columns in SQL, they're more commonly accepted as the standard for storing dates these days - There was a time when you'd be seen as a crazy person for doing so, but that time has passed. All of the major frameworks use DateTime in PHP and the appropriate DATE types in MySQL now.

ENUMs, however, I disagree with. They're fine if you're going to use MySQL strict mode, but in a situation where you can't control that (as IPS is,) I wouldn't go near them. In anything other than strict mode, MySQL will silently fail if you pass an invalid value for an ENUM column, storing a blank value in the database - they just can't be trusted.

The biggest improvement I could see for the IPS suite in terms of using the database is properly using foreign keys (incl. the ON UPDATE / DELETE functionality) and, where fitting, triggers. It can save an awful lot of code.

[Nimdock]

The thing with triggers is that debugging may become problematic. For a product that is not written by me I prefer to have the queries visible in the code.

I am also for the change to date/timestamps.

Enums are OK depending on what they are being used. But not just use them to use them.

[bfarber]

Some general comments....

We are working on using more appropriate data types in our MySQL schemas. Places where we use TINYINT we often use BIT now, and columns that store timestamps should be an integer column rather than a VARCHAR column.

It's an awful lot of work to convert timestamps to DATETIME in MySQL for no benefit... if there was some reason that motivated us to do so, we'd consider it, but as of right now I don't really see the benefit personally. It is important to remember that there are time zone considerations to take into account as well.

We will use ENUM (per my first point) if it is appropriate, but I agree with djpretzel - it is surprising how often a static list of possible values can change over time.

Views, triggers and stored procedures are far more difficult for us to take advantage of because some of this functionality is not always available on all hosting. Hosts can deny permission to create views and stored procedures for instance, and we have to account for if they are not available...making using them in the first place pretty pointless since we need an alternative fallback anyways.

[KT Walrus]

We are working on using more appropriate data types in our MySQL schemas. Places where we use TINYINT we often use BIT now, and columns that store timestamps should be an integer column rather than a VARCHAR column.

Good. I personally like TINYINT(1) (or BOOL, BOOLEAN) for booleans instead of BIT. I still think timestamps should be TIMESTAMP and not INT(10) because of all the built-in functions that convert, manipulate, display TIMESTAMPs.

It's an awful lot of work to convert timestamps to DATETIME in MySQL for no benefit... if there was some reason that motivated us to do so, we'd consider it, but as of right now I don't really see the benefit personally. It is important to remember that there are time zone considerations to take into account as well.

I guess we will have to disagree on this one. Using INT for DATETIME is kind of like using VARCHAR for integer data. Seems to me that if the database supports various DATETIME types, it should be used to represent values that are dates or times.

We will use ENUM (per my first point) if it is appropriate, but I agree with djpretzel - it is surprising how often a static list of possible values can change over time.

ENUM values can be extended easily enough. It is just a bit trickier to modifying/deleting enum literals. The real concern with ENUMs has been how easy invalid data can get silently lost. But, this can largely be avoided by always testing with STRICT SQL mode. As ENUMs aren't the only column type that can silently accept invalid data to get inserted, I would recommend you might enable STRICT mode on the IPS forums here to help ferret out any possible bugs in this area.

[Michael]

I guess we will have to disagree on this one. Using INT for DATETIME is kind of like using VARCHAR for integer data. Seems to me that if the database supports various DATETIME types, it should be used to represent values that are dates or times.

The posts table has 3 fields that currently store dates as integers instead of datetimes. It's not a trivial thing to go changing the data type on a field when a site has millions of rows of data in the old format. That's what bfarber is saying, the work required to convert that old data is risky for little actual benefit, seeing as how the current system does work.

If the software was being designed for version 1.0 now, then this would be a worthwhile thing to try and undertake, but IPS has lots of big sites they need to support and changing all of the date fields in every table is a huge risk.

[Ryan H.]

One advantage of date/datetime fields would be that you avoid any potential Y2038 problems. Given the logistics of changing, though, that's probably not sufficient grounds.

[Mark]

We did consider DATETIME quite a bit (which would be more appropriate than TIMESTAMP as for the latter we'd have to account for timezone issues, which would be particularly interesting if it changes in one connection such as when logging in), however decided not to. As I recall the reasons were:

• Updating the existing database would be extremely problematic.
• While in 4.0, dates are handled at the PHP end by objects of a class which extends DateTime, it is extremely common to send the current timestamp the database - for this purpose, this:

  time()

  is much simpler than:

  gmdate('Y-m-d H:is')

  or:

  ( new IPSDateTime )->getTimestamp()

• DATETIME requires double the amount of storage (8 bytes) to INT (4 bytes)

For me, the first of those is a show-stopper, especially for something which has very limited practical benefit. As Michael said - if we were writing 1.0, we would use it, but we're not :p

[bfarber]

I still think timestamps should be TIMESTAMP and not INT(10) because of all the built-in functions that convert, manipulate, display TIMESTAMPs.

I assume you are aware, but you can use many MySQL date/time functions against raw timestamps stored in an integer column already by leveraging from_unixtime().

SELECT * FROM `core_applications` WHERE year( from_unixtime( app_added ) ) = 2013;

[KT Walrus]

I assume you are aware, but you can use many MySQL date/time functions against raw timestamps stored in an integer column already by leveraging from_unixtime().

Yes. I am aware of this. In fact, in my ipb database, I have created table views of the major tables that convert to TIMESTAMP these INT(10) timestamp columns. It helps when I want to browse these tables in phpmyadmin to see readable timestamps.