Deleting old data from table

Hi

Have a simple task - need to delete old data from table;

in table I have datetime column.

for example to delete all that more than 3 month in TSQL I can do 

delete from table where datadiff('month', date, getdate()) > 3;

Ho to do it fast TS in? 

 

1) I have not found now()/getdate() function in TS. Does TS has such command?

2) I could do it using tql.

But how to use tql in bash with query or file with parameters?

Can I run 

tql -q $query

where $query defined above ? 

I found that I can use parameter -input_sql_file to execute query from file but I need put params inside - in my case oldest date.

Is it possible to use parameters in script file for example use "?" symbol and replace to variables from tql param.

3) third way is to use max value from the same table or from or other table. For this i need to use join but its also impossible 

 

So now its very uncomfortable to do it - only 1 way I see:

Create file with sql query with template for date value replace that template with sed to a new value and run that script using -input_sql_file

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Aleksei - you can echo a TQL command to TQL, i.e. "delete from tablex where date_column < your_date;"  

    There is a bash file on community tools called "generate_deletes" that you can just set up using configuration.  https://github.com/thoughtspot/community-tools/blob/master/generate_deletes/generate_deletes . It will also show the type of commands that are possible.  

    Reply Like 1
  • Thank you! 

    It works:

    echo "select count(*) from tablex;" | tql

    Much better.

    What about date funcs - for now we can't use now() or getdate(). Is it right? That will be much more easier. 

    Reply Like
  • But as the command Bill gave you is basically a bash/shell command, you can use bash/shell date functions, for example:

    archive_date=`date --date="7 days ago" +%Y-%m-%d`
    echo "delete from <table> where date <= \"${archive_date};\" " | tql

    (Note: this is answered on top of my head, but it should be along these lines)

    Reply Like 2
  • Thanks.

    Yep I've done right in such way. But Just still keep trying to get more knowledge of tql - some functions are exists like day(), month(), date() (undocumented). but there is no now().

    Reply Like
  • Hi Aleksei Burenin

    try doing:

    select * from table where Date_Column = today(); 

    or

    Select today() from table

    Reply Like
  • Thank you 

    Also found that possible to use other commands

    select d,                -- table column
        today() ,            -- get current date
        today()-0,           -- convert to epoch till seconds
        date(today()-0),     -- converting back from epoch
        time(d-1),           -- get time part from d-1
        date(today()-d),     -- diff in days in format yyyy-mm-dd from yyyy = 1970
        time(today()),       --time from today() always 00:00:00
        datediff(today(),d), -- difference in days
        datediff(d,today()), -- diff can be less when 0
        dateadd(today(),1),  -- add days to date
        dateadd(today(),-1)  -- remove days from date
    from test.test_date_del;
    
    

    result example:

    d|EXPR1001|EXPR1003|EXPR1004|EXPR1007|EXPR1009|EXPR1010|EXPR1011|EXPR1012|EXPR1013|EXPR1015
    -------------------------------------------------------------------------------------------
    2019-10-10 01:01:01|2019-11-27|1574787600|2019-11-27|01:01:00|1970-02-18|00:00:00|48|-48|2019-11-28|2019-11-26
    2019-11-25 10:01:01|2019-11-27|1574787600|2019-11-27|10:01:00|1970-01-02|00:00:00|2|-2|2019-11-28|2019-11-26
    2019-11-25 00:00:00|2019-11-27|1574787600|2019-11-27|23:59:59|1970-01-03|00:00:00|2|-2|2019-11-28|2019-11-26

    This is a good list of commands but good feature to add type of interval to dateadd, datediff 

    But still have a question 

    In tql, sql_cli i can see only "a few command":

    TQL [database=bi_ceg]> help;
    tql is a command line interface for creating schemas and performing basic database administration.
    All commands MUST end with ;
    Commands can optionally be multi-line.
    Few common commands
    -----------------------
    show databases;       -> list all available databases
    
    
    For a list of all commands, type "help;" after invoking tql
    

    I have tried to run help; but always see the same help message

     

    How to get list of ALL commands?

     

    TQL [database=(none)]> help
    tql is a command line interface for creating schemas and performing basic database administration.
    All commands MUST end with ;
    Commands can optionally be multi-line.
    Few common commands
    -----------------------
    
    Reply Like
Like Follow
  • Status Answered
  • 2 wk agoLast active
  • 6Replies
  • 18Views
  • 4 Following