Page 1 of 1

timezone problem

Posted: Sun Mar 04, 2007 11:12 pm
by johnhelen
The Ninja Space Goat | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello all. 

I have a table as the following:

[syntax="sql"]		CREATE TABLE redirects_hit_temp (
			from_url text NOT NULL, 
			min text NOT NULL, 
			time_hit timestamptz NOT NULL DEFAULT current_timestamp
)
Then I insert some values and use a query: "Select * from redirects_hit_temp", I have:



from_url_1 | 11 | 2007-03-05 16:33:39.303525+13
from_url_2 | 22 | 2007-03-05 17:02:10.367414+13


Now I want to use query to select all records above, I tried:

Code: Select all

$current_date = date("Y-m-d h:i:s");
echo $current_date;
$host = "localhost";
$user = "postgres";
$pass = "";
$db = "myapp";
$connection = pg_connect ("host=$host dbname=$db user=$user password=$pass");

$sql ="SELECT  h.from_url, h.min, h.time_hit FROM redirects_hit_temp h WHERE h.time_hit < '".$current_date."'";
However, I cannot get any result. Suppose that I run this query at 6:01PM (18:01) and print out the query, I got:

Code: Select all

SELECT h.from_url, h.min, h.time_hit FROM redirects_hit_temp h WHERE h.time_hit < '2007-03-05 06:01:03'

It look like the query has a wrong current_date !!!!!!!!.

It should be "2007-03-05 18:01:03+13"

How I can fix this timezone



Many thanks
john


The Ninja Space Goat | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Mar 04, 2007 11:23 pm
by feyd
Your call to date() doesn't include a request for timezone information nor the extra fractions of a second.

Why not use "current_timestamp"?