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
Select Distinct Values from One Table and Join With Another Table
Retrieve Varbinary Value as Base64 in Mssql
How to Search Json Array in MySQL
How to Get Only Digits from String in MySQL
How to Extract Part of a String in Hive
Sql Server How to Find the Customers Who Have Bought a Product from Each Store
Solve Query for Showing Top 5 Selling Products
How to Convert a Timestamp (Date Format) to Bigint in SQL
How to Convert Nvarchar to Numeric in SQL Server
Delete Rows With Date Older Than 30 Days With SQL Server Query
Sql Server How to Return Null Instead of 0 If a Grouped Value Doesn't Have Rows in Source
Extract Number from String With Oracle Function
Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )
List All Employee'S Names and Their Managers by Manager Name Using an Inner Join
Select Rows Within Last Complete Minute
Hive Explode List from Json-String
Sqlstate[Hy000] [2002] Php_Network_Getaddresses: Getaddrinfo Failed: Name or Service Not Known