PostgreSQL view output older than 90 days Post: 302532655

Sponsored Content

Top Forums Shell Programming and Scripting PostgreSQL view output older than 90 days Post 302532655 by mykey242 on Tuesday 21st of June 2011 03:02:47 PM

06-21-2011

Registered User

PostgreSQL view output older than 90 days

I am trying to find the correct syntax to give me row output only older than 90 days. I am using psql NOT mySQL or MSSQL!

Here is the sql command i am running

Code:

SELECT 
  dataset.id,
  timezone('UTC', dataset.create_time)::date,
  history.name as History_Name,
  history.id,
  hda.name as File_Name,
  dataset.file_size
from
  dataset,
  history_dataset_association hda,
  history,
  job_to_output_dataset,
  job
where
  hda.dataset_id = dataset.id
  AND
  hda.history_id = history.id
  AND
  job_to_output_dataset.dataset_id = hda.id
  AND
  job.id = job_to_output_dataset.job_id
  AND
  job.tool_id like '%import%'
  AND
  dataset.file_size >   1073741824 
  AND
  dataset.purged = false
;

and this is my output file:

Code:

ID    timezone    history_name    id    file_name        size    
114601  2011-05-23    LID8661        5604    LID8661_FC61JL.txt.gz    8418255538
85750   2010-07-07    Unnamed history 4249    s_3_1_sequence.tmp    7992819712
85751   2010-07-07    Unnamed history 4249    s_3_2_sequence.tmp    7973703680
85748   2010-07-07    Unnamed history 4249    s_2_1_sequence        7670258636
85749   2010-07-07    Unnamed history 4249    s_2_2_sequence        7670258636
127814  2011-05-21     AC_testis_52-68 6311    s_6_sequence        7103900102
127868  2011-05-23    Unnamed history 6296    s_3_sequence        6680231672
128274  2011-05-26    5-26-11        6346    s_3_sequence         6680231672
83539   2010-06-13    AJ HD5-S        4472    s_6_1_sequence        5991875692
83540   2010-06-13    AJ HD5-S        4472    s_6_2_sequence        5991875692
98346   2010-09-24    dfmod24 Kc pA NT 5062   LID9058_FC61W.txt.gz    5864636496
83974   2010-06-17    Unnamed history 4486    LID8659_FC61W.txt.gz    5689413616
83988   2010-06-17    Unnamed history 4486    LID8660_FC61W.txt.gz    5612981394
98345   2010-09-24    dfmod23 S2 pA NT5061    LID9057_FC61WW7A.txt.gz    5430755938
126701  2011-05-11    293T_sigma.txt    6240    293T_R_sigma        5377059342

mykey242

View Public Profile for mykey242

Find all posts by mykey242

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How can I delete files older than 7 days?

I will like to write a script that delete all files that are older than 7 days in a directory and it's subdirectories. Can any one help me out witht the magic command or script? Thanks in advance, Odogboly98:confused:

2. UNIX for Dummies Questions & Answers

Removing files older than 7 days

Script help, I need to delete files that are older than 7 days. I do that automatically but I know that a cron job can do the job for me. Any help is greatly appreciated, as you can see, I am a DOS or WINDOWS guy. Little on UNIX. Thanks

3. Shell Programming and Scripting

List directory 7 days older

Say folder archive/ contains many folder each created on a day. this folder may contain files. i want to write a script to delete all the folder inside archive/ which are 7 days older. i used the below script for the reason. find archive -mtime +7 -type d -exec rm -r {} \; pls suggest me if...

4. Shell Programming and Scripting

Deleting files older than 7 days

Hi Guys, I want to delete folder/files older than 7 days. Im using the command below. find /test/test1 -mtime +7 -print0 | xargs -0 rm -Rf /test/test1/* which works ok, but it deletes the test1 folder as well which i dont want. The test1 folder will have a list of sub-folders which in...

5. UNIX for Dummies Questions & Answers

Delete files older than 30 days

This is driving me crazy. How can I delete files in a specifc directory that are over 30 days old? Thanks in advance.

6. Solaris

Delete files older than 30 days

Hi all, I want to delete log files with extension .log which are older than 30 days. How to delete those files? Operating system -- Sun solaris 10 Your input is highly appreciated. Thanks in advance. Regards, Williams

7. Shell Programming and Scripting

Delete folders older than 30 days

Dear all, i use incremental backup my data with .zip to my hard drive. what i need is i don't want the old .zip file older than 30 days. how to write a shell script automatically remove my external hard disc zip backup folders older than 30 days? Regards,

8. UNIX for Dummies Questions & Answers

Files older than 50 days

Hi All, OS :- HP-UX wm5qa B.11.23 U ia64 1119805695 unlimited-user license I need to search files older than 50 days. I've used following command in order to search desired files, I also discoverd, it's showing today's files as well. Do you have any clue with this ? wmqa1> find ....

9. Shell Programming and Scripting

Listing files older than 30 days

Hi, Could someone help me that what the problem is in this code? #!/bin/sh FOLDER=/abc/datasource/checkstatus TIMESTAMP=$(date +%s) for filename in $(find $FOLDER -maxdepth 1 -type f -name "CHECK_STATUS*"); do f1=$($filename -Eo "{4}+") f2=$(date -d "$f1" +%s) if...

LEARN ABOUT DEBIAN

sysstat

SYSSTAT(5)							Linux User's Manual							SYSSTAT(5)

NAME

       sysstat - sysstat configuration file.

DESCRIPTION

       This  file is read by sa1(8) and sa2(8) shell scripts from the sysstat's set of tools.  It consists of a sequence of shell variable assign-
       ments used to configure sysstat logging.  The variables and their meanings are:

       HISTORY
	      The number of days during which a daily data file or a report should be kept. Data files or reports older than this number  of  days
	      will be removed by the sa2(8) shell script.

       COMPRESSAFTER
	      Number of days after which daily data files are to be compressed, either by gzip or bzip2.

       SADC_OPTIONS
	      Options  that  should be passed to sadc(8).  With these options (see sadc(8) manual page), you can select some additional data which
	      are going to be saved in daily data files.  These options are used only when a new data file is created. They will be  ignored  with
	      an already existing one.

FILES

       /etc/sysstat/sysstat

AUTHOR

       Sebastien Godard (sysstat <at> orange.fr)

SEE ALSO

       sadc(8), sa1(8), sa2(8)

       http://pagesperso-orange.fr/sebastien.godard/

Linux								  SEPTEMBER 2010							SYSSTAT(5)

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How can I delete files older than 7 days?

Discussion started by: odogbolu98

2. UNIX for Dummies Questions & Answers

Removing files older than 7 days

Discussion started by: texasoeb

3. Shell Programming and Scripting

List directory 7 days older

Discussion started by: krishnarao

4. Shell Programming and Scripting

Deleting files older than 7 days

Discussion started by: shezam

5. UNIX for Dummies Questions & Answers

Delete files older than 30 days

Discussion started by: tlphillips

6. Solaris

Delete files older than 30 days

Discussion started by: William1482

7. Shell Programming and Scripting

Delete folders older than 30 days

Discussion started by: joneggk

8. UNIX for Dummies Questions & Answers

Files older than 50 days

Discussion started by: alok.behria

9. Shell Programming and Scripting

Listing files older than 30 days

Discussion started by: Home

LEARN ABOUT DEBIAN

sysstat