2012-06-22 05:50
浏览 74

PHP / MySQL - 以正确的格式准备字符串以插入日期时间SQL列

In my UI the dates are shown like this - hh:ii:ss. The users are able to edit/add new dates and most probably they'll try to use the same format (24.06.2012 15:35:00) which can not be used for a SQL query. Here is what I've done till now:

$dt = (date_parse_from_format("d.m.Y H:i:s", $data['event_time']));
        $newdate = sprintf("%02d-%02d-%04d %02d:%02d:%02d" , $dt['day'], $dt['month'], $dt['year'], $dt['hour'], $dt['minute'], $dt['second']);
        $test = date("Y-m-d H:i:s", strtotime($newdate));
        if ($test == "1970-01-01 01:00:00")
            throw new Exception('Invalid date');

What happens is - if I leave the check if ($test == "1970-01-01 01:00:00") I get an exception, but if I comment the $test = date("Y-m-d H:i:s", strtotime($newdate)); line and the check the date is inserted only with zeros. $newdate is a sting in the right format for SQL - YYYY-mm-dd H:i:s but obv. I miss something here. How to insert this string as a valid SQL datetime?



图片转代码服务由CSDN问答提供 功能建议

在我的UI中,日期显示如下 - hh:ii:ss < /代码>。 用户可以编辑/添加新日期,并且很可能他们会尝试使用不能用于SQL查询的相同格式(24.06.2012 15:35:00)。 这是我到目前为止所做的:

  $ dt =(date_parse_from_format(“dmY H:i:s”,$ data ['event_time'])); \  n $ newdate = sprintf(“%02d-%02d-%04d%02d:%02d:%02d”,$ dt ['day'],$ dt ['month'],$ dt ['year'],$  dt ['hour'],$ dt ['minute'],$ dt ['second']); 
 $ test = date(“Ymd H:i:s”,strtotime($ newdate)); 
 if  ($ test ==“1970-01-01 01:00:00”)

会发生什么 - 如果我离开支票 if($ test ==“1970-01-01 01:00:00”)我得到一个例外,但如果我评论 $ test = date(“Ymd H:i:s”,strtotime($ newdate)); 行和检查日期仅用零插入。 $ newdate 是 SQL的正确格式 - YYYY-mm-dd H:i:s 但是obv。 我想念这里的东西。 如何将此字符串作为有效的SQL日期时间插入?



  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • dongzhi6146 2012-06-22 06:03

    The DateTime class was introduced in PHP 5.2 and would allow you to use something like this

    $dt = DateTime::createFromFormat("d.m.Y H:i:s", $data['event_time']);
    if($dt === false){
      throw new Exception("Invalid date");

    DateTime::createFromFormat returns false on failure (This method is only available since PHP 5.3)

    Then when saving to the database you can use the following to get the correct format for MySQL

    $dt->format("Y-m-d H:i:s")
    解决 无用
    打赏 举报

相关推荐 更多相似问题