Solved

Weird date problem with MSSQL and PHP

Posted on 2004-09-09
19
871 Views
Last Modified: 2012-06-21
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
Comment
Question by:maunded
  • 10
  • 2
  • 2
  • +4
19 Comments
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 12014952
So what's the problem exactly?
0
 
LVL 1

Author Comment

by:maunded
ID: 12015093
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
 
LVL 1

Author Comment

by:maunded
ID: 12015250
I should add I have mssql.datetimeconvert set to On in the PHP.ini If this makes a difference
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 12015446
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
 
LVL 1

Author Comment

by:maunded
ID: 12015629
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
 
LVL 1

Author Comment

by:maunded
ID: 12015805
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
 
LVL 3

Expert Comment

by:Sasho
ID: 12017983
What is the datatype of your ReceivedTimeStamp  column?
0
 
LVL 10

Expert Comment

by:frugle
ID: 12020766
I make SQL do the work - SELECT DATE_FORMAT(ReceivedTimeStamp,'%D %M, %Y') AS rcvdate

Mike
0
 
LVL 1

Author Comment

by:maunded
ID: 12022533
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Expert Comment

by:Diablo84
ID: 12028229
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
 
LVL 1

Author Comment

by:maunded
ID: 12031004
Hmmmm.....Ive had to use DATEADD(m,+1...... to each of my select statements!!
0
 
LVL 3

Expert Comment

by:Sasho
ID: 12031322
That's just wrong :) I cannot belive this is what you have to do...
0
 
LVL 1

Author Comment

by:maunded
ID: 12032620
Is anyone else using PHP5 on Redhat9 with apache2 and freetds that is having this problem with mssql?
0
 
LVL 15

Expert Comment

by:JakobA
ID: 12033492
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
 
LVL 1

Author Comment

by:maunded
ID: 12035813
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
 
LVL 1

Author Comment

by:maunded
ID: 12036089
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
 
LVL 1

Author Comment

by:maunded
ID: 12036226
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
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12083749
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now