[GRLUG] Postgresql help

[Ben Rousch]

On Tue, Apr 12, 2011 at 2:10 PM, Richard Maloley II
<richard at rrcomputerconsulting.com> wrote:
>
> Hello,
> I'm trying to create a really simple query against a Postgresql database. This database creates a new table every single day for daily statistics, therefore all tables are named the same except for the different date appended to the name.
> My query right now is simple: Execute a single SQL statement that will gather data from the correct daily table depending on the day. For example:
> SELECT * FROM tnt_audit.summaryreport_login_stats_20110412;
> The above query will select all of the data for today. The date piece, 20110412, needs to be dynamically changed. Here is what I thought of:
> SELECT
> *
> FROM
> tnt_audit.summaryreport_login_stats_(CURRENT_DATE)
> ;

I assume you're not just running ad-hoc queries for the heck of it, so
why not build the query in the calling program? Something like this in
Python:

from datetime import date, timedelta
def get_audit_for_date(use_date=date.today()):
    return "SELECT * FROM tnt_audit.summaryreport_login_stats_" +
use_date.strftime('%Y%m%d')
yesterday = get_audit_for_date(date.today() + timedelta(-1))
print yesterday
# output: SELECT * FROM tnt_audit.summaryreport_login_stats_20110411
xmas2010 = get_audit_for_date(date(2010,12,25))
print xmas2010
# output: SELECT * FROM tnt_audit.summaryreport_login_stats_20101225

--
 Ben Rousch
    brousch at gmail.com
   http://clusterbleep.net/

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.