[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

Weird date problem with MSSQL and PHP

I have a date problem that I cant figure out....or am I not looking at it right?....
Heres the table I am querying on:

2004-09-04 00:00:00.000      185      user      500.5
2004-09-04 00:06:00.000      182      user      500.5
2004-09-04 00:12:00.000      179      user      500.5
2004-09-04 00:18:00.000      177      user      500.5
2004-09-04 00:24:00.000      174      user      500.5

Heres the PHP.....
$query = "SELECT * FROM ST_Tide_Gauge_View WHERE (CustomerUserName = 'user') AND (TerminalID = '500.5') AND (GMT BETWEEN DATEADD(d, - 7, '$from_date') AND '$from_date')";
      $result = mssql_query($query);
      while ($data = mssql_fetch_array($result)) {
            $mydate = substr(($data['GMT']),0,19);
            $unixtime = strtotime($mydate);
              $backtotime = strftime("%Y-%m-%d %H:%M:%S",$unixtime);
            echo $unixtime,'<br>';
            echo $mydate,'<BR>';
            echo $backtotime,'<BR>';

And heres the output....
1091541600
2004-08-04 00:00:00
2004-08-04 00:00:00
1091541960
2004-08-04 00:06:00
2004-08-04 00:06:00
1091542320
2004-08-04 00:12:00
2004-08-04 00:12:00
1091542680
2004-08-04 00:18:00
2004-08-04 00:18:00
1091543040
2004-08-04 00:24:00
2004-08-04 00:24:00

Any ideas??
0
maunded
Asked:
maunded
  • 10
  • 2
  • 2
  • +4
1 Solution
 
Marcus BointonCommented:
So what's the problem exactly?
0
 
maundedAuthor Commented:
THe problem is the date is exactly a month behind....the date in the output is 2004-08-04 etc instead of 2004-09-04
Am I performing some sort of date subtraction function I cant see?
0
 
maundedAuthor Commented:
I should add I have mssql.datetimeconvert set to On in the PHP.ini If this makes a difference
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Marcus BointonCommented:
It doesn't look like you're doing anything wrong. Check that there's no confusion between reserved words by just doing a plain SELECT *, since you appear to have a field called GMT, which is also an MSSQL keyword. Could try renaming the field as well.
0
 
maundedAuthor Commented:
Nup, its still the same.  Ive tried it on different tables as well...could this be a bug in PHP?
Im using PHP5 also.
0
 
maundedAuthor Commented:
Heres more results:
SQL query and table:
SELECT TOP 5 ReceivedTimeStamp FROM ST_Aero2_View
2004-09-06 22:14:56.000
2004-09-06 22:14:56.000
2004-09-06 22:14:56.000
2004-09-06 22:04:55.000
2004-09-06 22:04:55.000

PHP query and results:
$query = "SELECT TOP 5 ReceivedTimeStamp FROM ST_Aero2_View";
      $result = mssql_query($query);
      while ($data = mssql_fetch_array($result)) {
      $mydata[] = $data;
};
print_r ($mydata);
Array ( [0] => Array ( [0] => 2004-08-06 22:14:56 [ReceivedTimeStamp] => 2004-08-06 22:14:56 ) [1] => Array ( [0] => 2004-08-06 22:14:56 [ReceivedTimeStamp] => 2004-08-06 22:14:56 ) [2] => Array ( [0] => 2004-08-06 22:14:56 [ReceivedTimeStamp] => 2004-08-06 22:14:56 ) [3] => Array ( [0] => 2004-08-06 22:04:55 [ReceivedTimeStamp] => 2004-08-06 22:04:55 ) [4] => Array ( [0] => 2004-08-06 22:04:55 [ReceivedTimeStamp] => 2004-08-06 22:04:55 ) )

0
 
SashoCommented:
What is the datatype of your ReceivedTimeStamp  column?
0
 
frugleCommented:
I make SQL do the work - SELECT DATE_FORMAT(ReceivedTimeStamp,'%D %M, %Y') AS rcvdate

Mike
0
 
maundedAuthor Commented:
The timestamp field is datetime, and PHP sees it as a datetime field
Mike...that looks like MySQL to me Im using MSSQL...even so, using the mssql convert function dosent help
0
 
Diablo84Commented:
The problem seems to be local to the sql query

PHP test with:

<?php
 $time = "2004-09-04 00:00:00.000";
 $mydate = substr(($time),0,19);
 $unixtime = strtotime($mydate);
 $backtotime = strftime("%Y-%m-%d %H:%M:%S",$unixtime);
 echo $unixtime,'<br>';
 echo $mydate,'<BR>';
 echo $backtotime,'<BR>';
?>

Returns the correct output:

1094252400
2004-09-04 00:00:00
2004-09-04 00:00:00

Have you tried back quoting the field reference

$query = "SELECT * FROM ST_Tide_Gauge_View WHERE (`CustomerUserName` = 'user') AND (`TerminalID` = '500.5') AND (`GMT` BETWEEN DATEADD(d, - 7, '$from_date') AND '$from_date')"


That said i just read your second to last post, very strange.
0
 
maundedAuthor Commented:
Hmmmm.....Ive had to use DATEADD(m,+1...... to each of my select statements!!
0
 
SashoCommented:
That's just wrong :) I cannot belive this is what you have to do...
0
 
maundedAuthor Commented:
Is anyone else using PHP5 on Redhat9 with apache2 and freetds that is having this problem with mssql?
0
 
JakobACommented:
Googling the DATEADD function I have found several formats:
     DATEADD(   d,     -7, '$from_date')
     DATEADD(  day,   -7, '$from_date')
     DATEADD(  "d",   -7, '$from_date')
     DATEADD( "day", -7, '$from_date')

I could well imagine that different subsets have been chosen by various implementations.

You haad a space between '-' and 7. It should not matter, but I have removed it anyway to make the unary minus more recognizable.
0
 
maundedAuthor Commented:
JakobA - Im not sure which tree you are barking up, but I guess you didnt read my original problem.
Its not the dateadd function I am having the problem with, its the fact that the rsults that come out are exactly a month behind the actual results.  Its not the WHERE fucntion that causes this as I only have data in the table that ranges from Sep 1 to Sep 9, so there is absolutely no reason that the results displayed would be anywhere near August :)
0
 
maundedAuthor Commented:
Apologies if my last post seemed a bit rude, it wasnt intentional.
I have another question that relates to my problem, and it looks like this will be an on going issue.
Since I now have to convert the date, is it better to do this in SQL or use a PHP function to convert each date after the query is done?  My webservers arnt really a good spec so it is better to do all the processing on the SQL server using the DATEADD query (at the expense of a little extra code) or write a PHP function to pass all my dates through?
0
 
maundedAuthor Commented:
OK, seems I might have found a solution...or even better, where the original problem was.
Please excuse the multiple posts :)

In the freetds locales config (/usr/local/etc/locales.conf), the default was set to :
date format = %Y-%b-%d %I:%M%p

I had mssql.datetimeconvert = Off
in the PHP config
Somewhere, somehow, when PHP was bringing in the datetime from my SQL table, it was converting this incorrectly, Im not really sure why or how.

Solution:
freetds locales file should be set to
date format = %Y-%m-%d %I:%M%p
and comment out the
mssql.datetimeconvert = Off

This is a format PHP can work with, so converting the time stamp into any other format is simple!
Thanks to eveyone that tried to help.
0
 
ee_ai_constructCommented:
Please read entire thread.  Asker solved or content important.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now