What is the preferred format to store date/times in a SQL Server database when PHP is your primary language?

Question

I am planning a PHP application that needs to store date/times in an MSSQL database. (For the curious, it is a calendar application.) What is the preferred format to store this information?

MSSQL has its own datetime data type, which works well in the database itself and is very readable. However, there aren't any MSSQL functions to translate datetime values to PHP's preferred format--UNIX timestamp. This makes it a bit more painful to use with PHP. UNIX timestamp is attractive because that's what PHP likes, but it's certainly not as readable and there aren't a bunch of nice built-in MSSQL functions for working with the data.

Would you store this information as datetime data type, as UNIX timestamps (as int, bigint, or varchar datatype), as both formats side by side, or as something else entirely?

Solution 1

I would store the dates in the MS-SQL format to assist in using the date manipulation functions in T-SQL to their fullest. It's easier to write and read

SELECT * FROM Foo
WHERE DateDiff(d,field1,now()) < 1

Than to try and perform the equivalent operation by manipulating integers

To convert a MsSQL date into a unix timestamp use dateDiff:

SELECT DATEDIFF(s,'1970-01-01 00:00:00',fieldName) as fieldNameTS
FROM TableName
WHERE fieldName between '10/1/2008' and '10/31/2008'

To Convert an Unix Timestamp into a MsSQL Date, you can either do it in PHP:

$msSQLDate = date("Y-m-d H:i:s", $unixDate );

or in MsSQL

INSERT INTO TableName ( 
  fieldName
) VALUES (
  DATEADD(s,'1970-01-01 00:00:00', ? ) 
) 

Where parameter one is int($unixDate)

Solution 2

I'd recommend the same as i do for all dates in any db engine, the db native type. (DATETIME)

Just use "YYYY-MM-DD HH:MM:SS" for inserting in php: date('Y-m-d H:i:s', $myTimeStampInSeconds);

-edit in response to comments below here -

1. for selected columns you can use $timestamp = strtotime( $yourColumnValue );
2. i recommend storing in the databas native format because you can then use SQL to compare records using SQL date/time functions like DATEADD() etc.

Solution 3

Hello and good day for everyone

Yes , might be thats the best way , store dates in db, they will take db format and you can format when you need as you wich

But there is another one solution in the ISO-developed international date format, i mean ISO 8601.

The international format defined by ISO (ISO 8601) tries to address all date problems by defining a numerical date system as follows: YYYY-MM-DD where

YYYY is the year [all the digits, i.e. 2100] MM is the month [01 (January) to 12 (December)] DD is the day [01 to 31] depending on moths :P

Using numerical dates does have also some pitfalls with regard to readability and usability it is not perfect.But ISO date format is, however, the best choice for a date representation that is universally (and accurately) understandable.

Note that this format can also be used to represent precise date and time, with timezone information

Here is a detailed information about ISO 8601:2000

http://www.iso.org/iso/support/faqs/faqs_widely_used_standards/widely_used_standards_other/date_and_time_format.htm

With no more.... Bye bye

Comments

[Joe Lencioni]

Inserting is easy, it's using the data after selecting it that is more of a nuisance.

[Joe Lencioni]

Also, you recommend storing it this way, but can you expand on why you recommend it?

[bart]

The thing I don't like in ISO-8601 date formats is the "T" between the date part and the time part. But the good part is that datetimes in this format sort well as plain strings.

[manhattan]

Joe Lencioni It's faster because it's just numbers, comparisons are numerical, and there is no way that a later date of a value less than a previous one. It is standardized (ISO8601) and it is not complicated to bring any kind of date to this string format (and its conversion to integer). While in Date type the program works to convert and make comparisons

[Kris]

@JoeLencioni: Because the database will know you're dealing with datetimes and i'll bet anything that the guys that wrote the date and time logic for any generally available database platform were a hell of a lot better at it than any random web developer that has to ask this question. Working with Date/Time values is hard, don't do it yourself.