Forum Stats

  • 3,837,090 Users
  • 2,262,225 Discussions
  • 7,900,202 Comments

Discussions

Delete datetime from SQL database based on hour

User_R2MXJ
User_R2MXJ Member Posts: 1 Green Ribbon

I'm a python dev, I'm handling an SQL database through sqlite3 and I need to perform a certain SQL query to delete data. I have tables which contain datetime objects as keys. I want to keep only one row per hour (the last record for that specific time)in python and delete the rest. I also need this to only happen on data older than 1 week.

Here's my attempt:

import sqlite3
c= db.cursor()
c.execute('''DELETE FROM TICKER_AAPL WHERE time < 2022-07-11 AND time NOT IN
    ( SELECT * FROM 
    (SELECT min(time) FROM TICKER_AAPL GROUP BY hour(time)) AS temp_tab);''')


Answers

  • BobDJ
    BobDJ Member Posts: 108 Bronze Badge
    edited Jul 21, 2022 3:29AM

    Welcome to the community.

    Please see guidelines Oracle Community General FAQ - oracle-tech

    What is "older than 1 week"? Is that past 7 days or the days before current week's Monday (July 18)?

    To get "the last record for that specific time" use MAX()

    For the days before current week's Monday (July 18)

    use to_char(created_on,'IW') < to_char(sysdate,'IW') in where clause

    or older than 7 days

    use created_on < sysdate-7 in where clause

    Using Monday as start of week in where condition

    delete targettable  
     where not exists (select 1 from
                                    (select to_char(trunc(created_on,'hh'), 'mm-dd-yyyy hh24:mi:ss') created_on,
                                            max(to_char(created_on, 'mm-dd-yyyy hh24:mi:ss')) maxtime 
                                    from users 
                                    where to_char(created_on,'IW')  < to_char(sysdate,'IW') 
                                    group by to_char(trunc(created_on,'hh'), 'mm-dd-yyyy hh24:mi:ss')
                                    ) temp
                              where temp.maxtime = to_char(targettable.created_on, 'mm-dd-yyyy hh24:mi:ss')
                     )
    
    
    

    If you want to change start of week to Sunday read Change Oracle's start of week to Sunday — oracle-tech

    to_char(created_on,'IW')
    

    The IW format inside to_char() represents the week of the year based on the ISO standard. In this case to_char(sysdate,'IW'), July 21st, is week 29 and dates before July 18th (Monday of current week) are weeks below 29.

    Hope this helps.

  • BobDJ
    BobDJ Member Posts: 108 Bronze Badge