Welcome to WebmasterWorld Guest from 54.204.219.143

Forum Moderators: open

Message Too Old, No Replies

Order by timestamp WITH simple math calculation

     
12:04 pm on Jul 14, 2012 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


Hello All -

I'm having trouble with a particular select and hoping someone here can help.

I'm selecting a table of information and want to order it by a timestamp field (fld_approved).

That's easy.

The trick that I'm trying to preform however is that I want the results sorted by the timestamp value of fld_approved + 3 days AND are LESS THAN the current time.

As mentioned, fld_approved contains an int timestamp (like 1342178312) and the 259200 shown below is 60*60*24*3 seconds (the seconds for 3 days).

What I've got now - which doesn't work at all - is:

"SELECT *
FROM $tbl_foo
ORDER BY fld_approved
WHERE (fld_approved + 259200) < UNIX_TIMESTAMP
LIMIT 0, 10";

I keep getting an error on the WHERE clause and can't figure out where I'm going wrong... or if I'm going about this query in the wrong way altogether.

Any guidance greatly appreciated.
2:53 pm on July 14, 2012 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5646
votes: 66


You need to put the WHERE before the ORDER BY:
"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < UNIX_TIMESTAMP
ORDER BY fld_approved
LIMIT 0, 10";
1:00 am on July 15, 2012 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


LifeinAsia -

Thanks very much... that should have been obvious to me.

Now, however, the error I'm getting is Unknown column 'UNIX_TIMESTAMP' in 'where clause'.

I would think that the WHERE clause is keying off fld_approved within the parens, but apparently not.

I've tried WHERE((fld_approved + 259200) < UNIX_TIMESTAMP) and other variations which would force the identification of fld_approved, but without any luck.

Any ideas?
1:26 am on July 15, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


UNIX_TIMESTAMP is a PHP command, so you need to either

1) execute that on the PHP side
"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < " + UNIX_TIMESTAMP + "
ORDER BY fld_approved
LIMIT 0, 10";
2) replace UNIX_TIMESTAMP with NOW()

"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < NOW()
ORDER BY fld_approved
LIMIT 0, 10";

My preference is for option 2.
1:51 am on July 15, 2012 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


Dijkgraaf -

Fantastic... have used option #2 and it works like a charm.

Thanks very much for the guidance!