?
Solved

Weird date problem with MSSQL and PHP

Posted on 2004-09-09
19
Medium Priority
?
879 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
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
 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses

764 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