Sql of note

Example of Time bucket

select
 from_unixtime(timestamp-timestamp%3600)as time,
 count(*)
from
 table
where
 data like 'Destination directory %does not exist at%' and
 FROM_UNIXTIME(timestamp) > subdate(now(),interval 4 day)
group by timestamp-timestamp%3600
order by time;

sqlite

.tables

.schema

Date / Time

These all return dates and times.

Last day of this month:

SELECT date('now','start of month','+1 month','-1 day');

the first day of this monnth:

SELECT date('now','start of month');

first day of last month:

SELECT date('now','start of month','-1 month');

last day of last month:

SELECT date('now','start of month','-1 day');

get epoch:

select strftime( '%s' , "2012-09-30") ;

convert from epoch to date:

SELECT datetime( 1092941466 , 'unixepoch');

convert back and forth:

SELECT datetime( strftime( '%s' , "2012-09-30") , 'unixepoch');

X in Y

SELECT filename FROM source WHERE hash NOT IN (SELECT hash FROM destination);

Also see

• Eve SQL of note
• EXPLAIN and Example of JOIN
• Properties Table

transpose wide tables

https://www.periscopedata.com/blog/using-row-numbering-and-full-joins-to-transpose-wide-tables

grouping

Redshift:

https://docs.aws.amazon.com/redshift/latest/dg/r_Examples_of_avg_WF.html

multiple agregates

https://blog.jooq.org/2017/04/20/how-to-calculate-multiple-aggregate-functions-in-a-single-query/