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?

Answer 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)