Sql of note: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
(→sqlite) |
|||
Line 51: | Line 51: | ||
SELECT datetime( strftime( '%s' , "2012-09-30") , 'unixepoch'); | 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 == | == Also see == |
Revision as of 22:49, 18 May 2018
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);