Welcome Guest, Not a member yet? Register   Sign In
Database Date & Time Format
#1

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?
Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10
Reply
#2

You have to use a function to format the date to what you desire, e.g. TO_CHAR, TO_DATE (Informix, DB2, and Oracle)
Simpler is always better
Reply
#3

(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?
Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10
Reply
#4

(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
Simpler is always better
Reply
#5

(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.
Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10
Reply
#6

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.
Reply
#7

(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.
Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10
Reply
#8

No, CI doesn't have anything to help with this.
Reply
#9

(This post was last modified: 02-08-2016, 10:22 AM by mwhitney. Edit Reason: get rid of smilies )

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.
Reply
#10

(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?
Kristian Matthews-Kennington
Apple Certified Associate Mac Integration & Management 10.10
Reply




Theme © iAndrew 2016 - Forum software by © MyBB