Eliminate Rows Based on Created Timestamp

Delete rows based on timestamp AND text in a separate column

It is very straightforward to do that.

Just get the values of column D and add an additional condition in the if statement:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Active Requests");//applies to active requests sheet only
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array
  
var currentDate = new Date();//today
var monthOld = Date.now() + -30*24*3600*1000; 
for (i=lastrow;i>=1;i--) {
var tempDate = values[i-1][0];// arrays are 0 indexed so row1 = values[0] and col1 = [0]
var bookCheck = values[i-1][3];// values in column D
if ( (tempDate!="") && (tempDate <= (monthOld)) && bookCheck=="Books")
{
  sheet.deleteRow(i);
}
}

How to drop rows based on timestamp where hours are not in list

First you can give your DataFrame a proper DatetimeIndex as follows:

dtidx = pd.DatetimeIndex(df['Date'].astype(str) + ' ' + df['Timestamp'].astype(str))
df.index = dtidx

and then use between_time to get the hours between hours 07 and 21 inclusive:

df.between_time('07:00', '22:00')
# returns
                         Date Timestamp   Close
2018-01-02 07:05:00  20180102  07:05:00   12926
2018-01-02 21:05:02  20180102  21:05:02 12925.5
2018-01-03 07:05:07  20180103  07:05:07 12925.8

Delete rows with date's before the required date point based on key value

You can just filter your dataframe using Boolean indexing. There is no groupwise operation here. Just remember to convert your series to datetime first.

df['date'] = pd.to_datetime(df['date'])

res = df[~(df['date'] < '2018-04-01')]

print(res)

   key_value       date
2   value_01 2018-04-02
3   value_01 2018-05-13
4   value_01 2018-05-16
7   value_02 2018-04-01
8   value_02 2018-05-16
9   value_02 2018-05-22
11  value_03 2018-04-14

How can I remove rows where their timestamp older than now?

Based on your example given, timestamp value of '1558180800' is UNIX_TIMESTAMP format. NOW() returns 'YYYY-MM-DD hh:mm:ss'. So if to directly translate your query:

DELETE FROM roompasswords WHERE timestamp > NOW();
is equivalent to
DELETE FROM roompasswords WHERE timestamp > '2019-05-14 09:18:06';

So it won't work given that it can't find that particular field value. Therefore, you need to change NOW() into UNIX_TIMESTAMP. Below are example you can try:

1) DELETE FROM roompasswords WHERE timestamp > UNIX_TIMESTAMP(NOW());

Note that NOW() returns current date + time so if you want to specify a time as well you can do the following instead:

2) DELETE FROM roompasswords WHERE timestamp > UNIX_TIMESTAMP('2019-05-14 00:00:00');

OR you can fetch the UNIX_TIMESTAMP value first then use that for your DELETE query:

  SELECT UNIX_TIMESTAMP('2019-05-14 00:00:00'); -- will return '1557763200'
  -- then
  DELETE FROM roompasswords WHERE timestamp > '1557763200';

Lastly, this query will work as well:

 3) DELETE FROM roompasswords WHERE FROM_UNIXTIME(TIMESTAMP) > '2019-05-14 00:00:00';
    or
    DELETE FROM roompasswords WHERE FROM_UNIXTIME(TIMESTAMP) > NOW();

Remove old duplicate rows in BQ based on timestamp

Delete rows based on timestamp minute

Try this:

DELETE FROM mytable 
WHERE (minute(dt) - 5) % 10 = 0

This deletes all rows whose minute value ends at 5 (if this is what you really want).

Simpler version of the above query (thanks to @Gordon):

DELETE FROM mytable 
WHERE minute(dt) % 10 = 5

MySQL Query to delete rows whose timestamp is older than current timestamp

delete from events where timestamp < NOW()

should be enough.

---

Related Topics