Database Date & Time Format

Using `$this->db->platform()` seems like the best idea, but given different drivers (such as MSSQL), prefer different formats, it seems like this should be part of the database library?

In most cases, the database library doesn't have any metadata for the fields you are working with, unless you request that data (and it usually just returns that data to you). In other words, the database library doesn't even know you're working with a date, and has no concept of what format is required for the various date formats supported by each platform.

Even when using Query Builder, the database library in CI is a relatively low-level library with a limited set of features and relatively low overhead.

Another option to consider would be to build a library in the manner of DB_Forge or DB_Utility, both of which load a driver based on which database platform is currently configured, but neither of which are loaded directly by the DB class (the way DB_Query_Builder is). This means that you could extend the loader to add another method, using the dbutil() and dbforge() methods as a reference, build an abstract class as the base for the library, then add the platform-specific drivers you need. So, it would be possible to add any number of features to the database library without having to modify the CI code directly.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

Using `$this->db->platform()` seems like the best idea, but given different drivers (such as MSSQL), prefer different formats, it seems like this should be part of the database library?

In most cases, the database library doesn't have any metadata for the fields you are working with, unless you request that data (and it usually just returns that data to you). In other words, the database library doesn't even know you're working with a date, and has no concept of what format is required for the various date formats supported by each platform.

Even when using Query Builder, the database library in CI is a relatively low-level library with a limited set of features and relatively low overhead.

Another option to consider would be to build a library in the manner of DB_Forge or DB_Utility, both of which load a driver based on which database platform is currently configured, but neither of which are loaded directly by the DB class (the way DB_Query_Builder is). This means that you could extend the loader to add another method, using the dbutil() and dbforge() methods as a reference, build an abstract class as the base for the library, then add the platform-specific drivers you need. So, it would be possible to add any number of features to the database library without having to modify the CI code directly.

I understand that, I'm not sure what I'm attempting to accomplish is being understood correctly, so let me try and start again.

The database library allows you to work with databases regardless of the database platform / driver being used, but as it stands, inserting dates into a database requires you to know what platform / driver the target is to correctly format the date.

I want to either be able to insert a native PHP date as the value and CodeIgniter parse that to the correct format, or be able to get the correct date and time format using something like `$this->db->date_time_format`.

If these are viable options, I'd like to create a pull request with the necessary changes.

Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10

Here's the problem - date handling seems trivial, but is rather complex instead. We, being humans, are used to handling dates, but building an algorithm to handle them is not as easy ... Timezone handling is a particularly subtle problem to solve. It's not as simple as knowing the format to use.

Plus, determining the format is a problem on its own - we have ~10 database platforms supported (a guess); even after you figure out the defaults, each of them will have the format configurable and/or accessible in completely different ways.

I'm not even sure if you know what you're saying by "a native PHP date". The only thing that comes close to that is the DateTime class, but it's not truly "native" in the sense that it's just another class; it does calculations and that's it.

Here's the problem - date handling seems trivial, but is rather complex instead. We, being humans, are used to handling dates, but building an algorithm to handle them is not as easy ... Timezone handling is a particularly subtle problem to solve. It's not as simple as knowing the format to use.

Plus, determining the format is a problem on its own - we have ~10 database platforms supported (a guess); even after you figure out the defaults, each of them will have the format configurable and/or accessible in completely different ways.

I'm not even sure if you know what you're saying by "a native PHP date". The only thing that comes close to that is the DateTime class, but it's not truly "native" in the sense that it's just another class; it does calculations and that's it.

Thanks for giving me a bit more context Narf. Given everything you've said, I'm still struggling to make my application database agnostic, perhaps the best way forward would be to use timestamps instead then, at least these can be stored without worried about the format.

Would you have any other suggestions?

Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10

Some database systems include a data type which they call a timestamp, but which is usually just one of the date/time formats they support. To store a UNIX timestamp, you have to consider the range of dates you'll be storing (for example, anything before 1970 is going to require some changes), and you have to ensure that the data type you choose for each platform will store the range of timestamp values you need. For example, in MySQL you would generally use an unsigned int, or a bigint, though you could get away with a signed int if you don't care about storing dates into or after 2038 (someone will care in 2037 or 2038 if your data is still around).

The 1970 starting point isn't a big issue for most people, unless you're storing historical data.

In Microsoft SQL Server or Postgre, you don't have unsigned integers, so you would usually use a bigint. Sounds good, we can just use a bigint everywhere, right?

No, because Oracle doesn't support bigint, and even integer isn't a built-in data type (though they have INTEGER, INT, and SMALLINT "subtypes" which will all give you the maximum space for an integer, as if you defined them with the NUMBER(38,0) type). Also, Oracle is one of those platforms with a TIMESTAMP data type, and the default format of that data type is determined by the NLS_TIMESTAMP_FORMAT parameter or the NLS_TERRITORY parameter.

In order to handle dates properly, you have to know the database platform and the data type. Usually, the developer has some control over the data type or knows ahead of time what that will be, and the database library can tell you the platform. For the database library to handle it for you would require not only the addition of extra code for each data type supported by the platform (or, in this case, at least every data type which might reasonably be used to store a date), but also that one or more additional calls be made to the database to get the data type information for each field (assuming the platform provides that information).

The second part is probably the big item that would prevent this from becoming part of the core database library. Many of CI's users prefer that the system not make additional calls to the database. This is why I mentioned creating an additional library in the mold of DB_Forge or DB_Utility.

An MS SQL driver for that library could determine that you're storing that DateTime object as an int, bigint, date, datetime2, time, datetime, datetimeoffset, or smalldatetime and use the appropriate format string with DateTime::format (or date_format()). Meanwhile, the MySQL library could determine whether you're using an unsigned int/bigint, date, datetime, timestamp, time, or year and do the same. Postgre would deal with bigint, date, timestamp, or time, and Oracle would deal with number, date, or timestamp. Something else to consider is that the library still doesn't know that your int/bigint/number fields are DateTime values when it retrieves them, unless your model explicitly converts them (or tells the library to do so).

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

Thanks for giving me a bit more context Narf. Given everything you've said, I'm still struggling to make my application database agnostic, perhaps the best way forward would be to use timestamps instead then, at least these can be stored without worried about the format.

Some database systems include a data type which they call a timestamp, but which is usually just one of the date/time formats they support. To store a UNIX timestamp, ...

I notice that you both (and most people really) interpret the "timestamp" as if it only means a UNIX timestamp ...

Everything that represents a point in time is a timestamp, "UNIX timestamp" is just yet another format. A lot of things will make more sense if you get used to that.

Would you have any other suggestions?

I'm not that familiar with the SQL standard and at this point I don't even remember how exactly these functions worked, but I'm pretty sure TO_CHAR(), TO_DATE() or another similar function would exist on pretty much all platforms.
That should give you the ability to pass a date in your preferred format (by explicitly specifying it in the call) and leave that function to convert it to the database's format. The same thing should be possible for fetching as well.

I'm really guessing here, but I see no reason why this shouldn't be possible on the DB level. It'd be absurd for every programming language/framework to have to build abstractions for that.

The problem is that the standard is flexible enough to allow an ecosystem in which it's almost impossible to write cross-platform SQL. Even if you could use TO_DATE() in MySQL (you can't), MySQL uses a different format specifier. So, in Oracle you write:

Code:

TO_DATE('2016-02-11', 'YYYY-MM-DD')

While in MySQL you write:

Code:

STR_TO_DATE('2016-02-11', '%Y-%m-%d')

In Postgre:

Code:

to_date('2016-02-11', 'YYYY-MM-DD')

(that's 2 with the same syntax, more or less)

DB2 (permitted formats are installation-dependent?!):

Code:

date('2016-02-11')

In MS SQL Server you would probably use CONVERT, CAST, or DATEFROMPARTS.

Here's what appears to be a pretty thorough reference for this mess:
https://en.wikibooks.org/wiki/SQL_Dialec..._functions

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

The problem is that the standard is flexible enough to allow an ecosystem in which it's almost impossible to write cross-platform SQL. Even if you could use TO_DATE() in MySQL (you can't), MySQL uses a different format specifier. So, in Oracle you write:

Code:

TO_DATE('2016-02-11', 'YYYY-MM-DD')

While in MySQL you write:

Code:

STR_TO_DATE('2016-02-11', '%Y-%m-%d')

In Postgre:

Code:

to_date('2016-02-11', 'YYYY-MM-DD')

(that's 2 with the same syntax, more or less)

DB2 (permitted formats are installation-dependent?!):

Code:

date('2016-02-11')

In MS SQL Server you would probably use CONVERT, CAST, or DATEFROMPARTS.

Here's what appears to be a pretty thorough reference for this mess:
https://en.wikibooks.org/wiki/SQL_Dialec..._functions

The differences here really are a mess which was why I suggested implementing a way of handling this using the CodeIgniter database drivers.

Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10

The differences here really are a mess which was why I suggested implementing a way of handling this using the CodeIgniter database drivers.

I understand that. The problem is that it's not just dates, or even just a handful of things. You might be able to get the functionality you're looking for by using an ORM library, but not all of them are going to be able to do that for you, and most of them are going to support fewer database platforms. In most cases, they support fewer platforms specifically because of these types of issues. Many of them also have a lot of other requirements for the structure of your database.

There's very little chance that this will be implemented in CI's core at any time in the near (or probably even the distant) future. That's why I've tried to explain some of the ways it might be possible to work around these issues.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide