Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Date operations

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: Oliver Smith <oliver(at)kfs(dot)org>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Date operations
Date: 1999-03-31 16:19:54
Message-ID: 37024B2A.541B46AA@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
> Question 1:
>  SELECT COUNT(bytes), SUM(bytes)
>    FROM tbl_robot_log
>   WHERE email = '$email' AND action = 'sent'
>     AND at >= datetime('now' + reltime('-60 mins'::timespan));
> When is the datetime(...) expression evaluated?

The function calls are probably evaluated once per query. And you can
simplify your query a bit:

  AND at >= ('now'::datetime + '-60 mins'::timespan);

> Question 2:
> What would be the most efficient way to get the combination of
> 1 and 24 hour logs? Should I get all entries within the last 24
> hours and use a 'group by' statement? If so; how would I do the
> group by? Is there a way to say :
>  GROUP BY age(at, 'hours')?

The function you want is date_part('hours', at). Not sure if the GROUP
BY would be happy with it, but istm that you might be able to select a
column of date_part() and then group on that...

                   - Tom

In response to

pgsql-hackers by date

Next:From: Thomas LockhartDate: 1999-03-31 16:21:35
Subject: Re: [HACKERS] Date operations
Previous:From: Thomas LockhartDate: 1999-03-31 15:47:04
Subject: Re: [HACKERS] [OT] Timezones and Daylight savings.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group