pgSQL date intervals

dante2010

Hi,

Can anyone tell me how I mimic the following mySQL statement in pgSQL?

select field1,field2 from table where date_field >= date_sub(now(), interval 7 day);

I need to grab records from X days ago..

barand


$thedate = date('Y-m-d', strtotime('- 7 days'));

$sql = "select field1,field2 from table where date_field >= '$thedate' ";

hth

dante2010

That doesn't work. I get the following error:

Error in query: SELECT * FROM petitions_active WHERE closed_by='63', timestamp >= '2003-06-02' .ERROR: parser: parse error at or near ","

sfullman

yeah, you don't use a comma, you use AND instead. It's all logical statements.

Sam

chriskl

There's a much better way of doing it, and guess what - it's SQL standard, rather than MySQL's random syntax:

select field1,field2 from table where date_field >= current_date - interval '7 days';

Chris

sfullman

I defer to anyone who knows their mySQL function list :-).

However be aware that too many complicated mySQL function calcs (certainly not this example) may actually be slower than having php parse things and sending simpler queries to the dtaabase.

Sam

Sxooter

Yeah, but this is a postgresql problem, and with Postgresql the philosophy of letting the database carry the load works quite well.

Seriously, load handling of stuff like this in pgsql is great. I've found Postgresql tends to be I/O bound, while MySQL is usually CPU bound.

chriskl

Trust me on this - do as little as possible date/time manipulation in PHP. You haven't considered problems like:

What if the timezone setting on your PHP machine is different to that on your DB machine. All your results will be out by 3 hours, etc.
What if there is clock drift between the two servers?
Even if they're on the same server, there is a different between wall clock time and transaction time. In PostgreSQL, current_timestamp doesn't change during a transaction (which is correct).
What if you use your PHP script in another timezone, eg. send it to someone else - it will break if you have hard coded time zone stuff...

Chris

Sxooter

hehe. Chris, I consider those to be "blank stare" questions. They're the kind of questions that I get a lot of blank stares from when I ask them at work.

Of course, the foxpro guys just say "What, code outside of your database? How quaint..."