Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

PostgreSQL view output older than 90 days

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting PostgreSQL view output older than 90 days
# 1  
Old 06-21-2011
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

# 2  
Old 06-21-2011
What is your unix/shell question?
# 3  
Old 06-21-2011
I run psql with command options from #!/bin/sh

i actually figured it out
Code:
  AND
  dataset.create_time < (current_date - interval '3 months')

was added to the end of the file.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. 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... (11 Replies)
Discussion started by: Home
11 Replies

2. 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 .... (4 Replies)
Discussion started by: alok.behria
4 Replies

3. 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, (2 Replies)
Discussion started by: joneggk
2 Replies

4. 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 (2 Replies)
Discussion started by: William1482
2 Replies

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. (3 Replies)
Discussion started by: tlphillips
3 Replies

6. 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... (4 Replies)
Discussion started by: shezam
4 Replies

7. 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... (3 Replies)
Discussion started by: krishnarao
3 Replies

8. 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 Replies)
Discussion started by: texasoeb
3 Replies

9. 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: (3 Replies)
Discussion started by: odogbolu98
3 Replies
Login or Register to Ask a Question