CodeIgniter Forums

Full Version: Database Date & Time Format
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
The accepted date and time formats accepted by different databases differs, how can I get the correct date and time format from the database driver that I'm currently using?
You have to use a function to format the date to what you desire, e.g. TO_CHAR, TO_DATE (Informix, DB2, and Oracle)
(02-01-2016, 02:59 PM)donpwinston Wrote: [ -> ]You have to use a function to format the date to what you desire, e.g. TO_CHAR, TO_DATE (Informix, DB2, and Oracle)

Could you give an example of what you mean please?
(02-01-2016, 03:03 PM)EpicKris Wrote: [ -> ]
(02-01-2016, 02:59 PM)donpwinston Wrote: [ -> ]You have to use a function to format the date to what you desire, e.g. TO_CHAR, TO_DATE (Informix, DB2, and Oracle)

Could you give an example of what you mean please?

SELECT TO_CHAR(some_date, '%m/%d/%Y') FROM some_table;

INSERT INTO some_table (some_date) VALUES (TO_DATE('12/14/1962', '%m/%d/%Y');

I don't know if there's a standard function that works with all dbs
(02-01-2016, 07:54 PM)donpwinston Wrote: [ -> ]
(02-01-2016, 03:03 PM)EpicKris Wrote: [ -> ]
(02-01-2016, 02:59 PM)donpwinston Wrote: [ -> ]You have to use a function to format the date to what you desire, e.g. TO_CHAR, TO_DATE (Informix, DB2, and Oracle)

Could you give an example of what you mean please?

SELECT TO_CHAR(some_date, '%m/%d/%Y') FROM some_table;

INSERT INTO some_table (some_date) VALUES (TO_DATE('12/14/1962', '%m/%d/%Y');

I don't know if there's a standard function that works with all dbs

The problem with this is that the format accepted for dates varies between different database drivers (for example MySQL and PostgreSQL use different date formats), I want to be able to use the date format dependant to whatever database driver might be being used.
Why not store the date/times values as an epoch value? That way you store the datetime as an integer so it is the same in all databases.

In MySQL: SELECT UNIX_TIMESTAMP(NOW())

Or in php: time() for current epoch or strtotime("next Thursday") etc.
(02-04-2016, 08:42 AM)Diederik Wrote: [ -> ]Why not store the date/times values as an epoch value? That way you store the datetime as an integer so it is the same in all databases.

In MySQL: SELECT UNIX_TIMESTAMP(NOW())

Or in php: time() for current epoch or strtotime("next Thursday") etc.

I want to store dates natively in SQL not as integers, does anyone know if what I'm asking for is included in the CodeIgniter framework or should be implemented? There's no knowing what database driver the application will be using in production.
No, CI doesn't have anything to help with this.
I would recommend adding a method to your project's base model to set dates to the desired format(s), then set the format based on the environment (and/or the value of $this->db->platform()).

After that, it's a matter of being consistent when creating date fields in your database, since many database platforms support multiple date and/or datetime formats. Otherwise, you might have to do something more complicated, like getting field metadata and setting the format based on that, or storing the information in a config file or in individual models to ensure that the fields are encoded properly.

In most cases, you can use either 'Y-m-d H:i:s' or 'Y-m-d\TH:i:s' as the format string in PHP when converting a DateTime to store in the database. I believe most databases have some support for the former, though it's limited in MS SQL, where the latter is preferred.
(02-08-2016, 10:06 AM)mwhitney Wrote: [ -> ]I would recommend adding a method to your project's base model to set dates to the desired format(s), then set the format based on the environment (and/or the value of $this->db->platform()).

After that, it's a matter of being consistent when creating date fields in your database, since many database platforms support multiple date and/or datetime formats. Otherwise, you might have to do something more complicated, like getting field metadata and setting the format based on that, or storing the information in a config file or in individual models to ensure that the fields are encoded properly.

In most cases, you can use either 'Y-m-d H:iConfused' or 'Y-m-d\TH:iConfused' as the format string in PHP when converting a DateTime to store in the database. I believe most databases have some support for the former, though it's limited in MS SQL, where the latter is preferred.

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?
Pages: 1 2