Solved

Weird date problem with MSSQL and PHP

Posted on 2004-09-09
19
870 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:Squinky
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:Squinky
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

708 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

12 Experts available now in Live!

Get 1:1 Help Now