当PHP是主要语言时，在SQL Server数据库中存储日期/时间的首选格式是什么？ [英] What is the preferred format to store date/times in a SQL Server database when PHP is your primary language?

问题描述

我正在计划一个需要在MSSQL数据库中存储日期/时间的PHP应用程序。 （对于好奇，它是一个日历应用程序。）存储此信息的首选格式是什么？

MSSQL有自己的datetime数据类型，在数据库本身很可读。但是，没有任何MSSQL函数将datetime值转换为PHP的首选格式 - UNIX时间戳。这使得使用PHP更加痛苦。 UNIX时间戳是有吸引力的，因为这是PHP喜欢的，但它肯定不是可读的，并且没有一堆漂亮的内置MSSQL功能来处理数据。

您将这些信息作为日期时间数据类型存储，作为UNIX时间戳（作为int，bigint或varchar数据类型），作为两种格式并排存在，还是作为其他完整的？

解决方案

我将以MS-SQL格式存储日期，以协助在T-SQL中使用日期操作功能。更容易编写和阅读

  SELECT * FROM Foo 
 WHERE DateDiff（d，field1，now（））< ; 1 
  

比通过操纵整数尝试执行等效操作

要将MsSQL日期转换为unix时间戳，请使用dateDiff：

  SELECT DATEDIFF（s，'1970- 01-01 00：00：00'，fieldName）as fieldNameTS 
 FROM TableName 
 WHERE fieldName between '10 / 1/2008'和'10 / 31/2008'
  

要将Unix时间戳转换为MsSQL日期，您可以在PHP中执行：

  $ msSQLDate = date（Ymd H：i：s，$ unixDate）; 
  

或MsSQL

  INSERT INTO TableName（
 fieldName 
）VALUES（
 DATEADD（s，'1970-01-01 00:00:00'，？）
） 
  

其中参数一是int（$ unixDate）

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?

解决方案

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)

这篇关于当PHP是主要语言时，在SQL Server数据库中存储日期/时间的首选格式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！