Menu
I am trying to get the last date an insert was performed in a table (on Amazon Redshift), is there any way to do this using the metadata? The tables do not store any timestamp column, and even if they had it, we need to find out for 3k tables so it would be impractical so a metadata approach is our strategy. Any tips?
682
There is a proper way to get table creation date and time in Redshift, that is not based on query log: SELECT TRIM(nspname) AS schema_name, TRIM(relname) AS table_name, relcreationtime AS creation_time FROM pg_class_info LEFT JOIN pg_namespace ON pg_class_info. relnamespace = pg_namespace.
LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the data type of the date argument.
Returns the date and time in UTC for the start of the current transaction. SYSDATE. TIMESTAMP. TIMEOFDAY. Returns the current weekday, date, and time in the current session time zone (UTC by default) as a string value.
All insert execution steps for queries are logged in STL_INSERT. This query should give you the information you're looking for:
SELECT sti.schema, sti.table, sq.endtime, sq.querytxt
FROM
(SELECT MAX(query) as query, tbl, MAX(i.endtime) as last_insert
FROM stl_insert i
GROUP BY tbl
ORDER BY tbl) inserts
JOIN stl_query sq ON sq.query = inserts.query
JOIN svv_table_info sti ON sti.table_id = inserts.tbl
ORDER BY inserts.last_insert DESC;
Note: The STL tables only retain approximately two to five days of log history.
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
