Searching efficiently based on dates in various DBMS

README.md

Backup of SQL Fiddle code for this StackOverflow answer: http://stackoverflow.com/a/10395492/1349518

mssqlserver.sql

create table dates(dte Date);

create index dte_idx on dates(dte);

insert into dates values (GetDate());
insert into dates values (GetDate() + 1);
insert into dates values (GetDate() - 1);


create table datetimes(dtm DateTime);

create index dtm_idx on datetimes(dtm);

insert into datetimes values (GetDate());
insert into datetimes values (GetDate() + 1);
insert into datetimes values (GetDate() - 1);


create table datetimes2(dtm2 DateTime2);

create index dtm2_idx on datetimes2(dtm2);

insert into datetimes2 values (GetDate());
insert into datetimes2 values (GetDate() + 1);
insert into datetimes2 values (GetDate() - 1);

----

select * from dates 
where dte = CAST(CURRENT_TIMESTAMP AS DATE);

select * from datetimes 
where dtm >= CAST(CURRENT_TIMESTAMP AS DATE)
and dtm < DATEADD(DD, 1, CAST(CURRENT_TIMESTAMP AS DATE))
;

select * from datetimes2
where dtm2 >= CAST(CURRENT_TIMESTAMP AS DATE)
and dtm2 < DATEADD(DD, 1, CAST(CURRENT_TIMESTAMP AS DATE))
;

mysql.sql

create table dates(dte Date);

create index dte_idx on dates(dte);

insert into dates values (NOW());
insert into dates values (NOW() + interval 1 day);
insert into dates values (NOW() - interval 1 day);


create table datetimes(dtm DateTime);

create index dtm_idx on datetimes(dtm);

insert into datetimes values (NOW());
insert into datetimes values (NOW() + interval 1 day);
insert into datetimes values (NOW() - interval 1 day);

----

select * from dates 
where dte = cast(now() as date)
;

select * from datetimes 
where dtm >= cast((now()) as date)
and dtm < cast((now() + interval 1 day) as date)
;

oracle.sql

create table dates(dte Date);

create index dte_idx on dates(dte);

insert into dates values (current_date);
insert into dates values (current_date + 1);
insert into dates values (current_date + 0.5);
insert into dates values (current_date - 0.5);
insert into dates values (current_date - 1);


create table timestamps(ts timestamp);

create index ts_idx on timestamps(ts);

insert into timestamps values (current_timestamp);
insert into timestamps values (current_timestamp + 1);
insert into timestamps values (current_timestamp + 0.5);
insert into timestamps values (current_timestamp - 0.5);
insert into timestamps values (current_timestamp - 1);


create table timestamps_local(tsl timestamp with local time zone);

create index tsl_idx on timestamps_local(tsl);

insert into timestamps_local values (current_timestamp);
insert into timestamps_local values (current_timestamp + 1);
insert into timestamps_local values (current_timestamp + 0.5);
insert into timestamps_local values (current_timestamp - 0.5);
insert into timestamps_local values (current_timestamp - 1);


create table timestamps_tz(tstz timestamp with time zone);

create index tstz_idx on timestamps_tz(tstz);

insert into timestamps_tz values (current_timestamp);
insert into timestamps_tz values (current_timestamp + 1);
insert into timestamps_tz values (current_timestamp + 0.5);
insert into timestamps_tz values (current_timestamp - 0.5);
insert into timestamps_tz values (current_timestamp - 1);
commit;

----

select to_char(dte, 'YYYY-MM-DD HH24:MI:SS') dte
from dates 
where dte >= trunc(current_date)
and dte < trunc(current_date) + 1
;

select to_char(ts, 'YYYY-MM-DD HH24:MI:SS') ts
from timestamps
where ts >= trunc(current_date)
and ts < trunc(current_date) + 1
;

select to_char(tsl, 'YYYY-MM-DD HH24:MI:SS') tsl
from timestamps_local
where tsl >= trunc(current_date)
and tsl < trunc(current_date) + 1
;

select to_char(tstz, 'YYYY-MM-DD HH24:MI:SS') tstz
from timestamps_tz
where tstz >= trunc(current_date)
and tstz < trunc(current_date) + 1
;

postgresql.sql

create table dates(dte Date);

create index dte_idx on dates(dte);

insert into dates values (current_date);
insert into dates values (current_date + interval '1 day');
insert into dates values (current_date - interval '1 day');


create table timestamps(ts timestamp without time zone);

create index ts_idx on timestamps(ts);

insert into timestamps values (current_timestamp);
insert into timestamps values (current_timestamp + interval '1 day');
insert into timestamps values (current_timestamp + interval '12 hours');
insert into timestamps values (current_timestamp - interval '12 hours');
insert into timestamps values (current_timestamp - interval '1 day');


create table timestamps_tz(tstz timestamp with time zone);

create index tstz_idx on timestamps_tz(tstz);

insert into timestamps_tz values (current_timestamp);
insert into timestamps_tz values (current_timestamp + interval '24 hours');
insert into timestamps_tz values (current_timestamp + interval '12 hours');
insert into timestamps_tz values (current_timestamp - interval '12 hours');
insert into timestamps_tz values (current_timestamp - interval '24 hours');

----

select * from dates 
where dte = current_date
;

select * from timestamps order by ts;

select * from timestamps
where ts >= 'today'
and ts < 'tomorrow'
;

select * from timestamps_tz order by tstz;

select * from timestamps_tz
where tstz >= 'today'
and tstz < 'tomorrow'
;

sqlite.sql

create table dates(dte varchar(10));

create index dte_idx on dates(dte);

insert into dates select date('now');
insert into dates select date('now', '+1 day');
insert into dates select date('now', '+1 day');


create table datetimes(dtm varchar(10));

create index dtm_idx on datetimes(dtm);

insert into datetimes select datetime('now');
insert into datetimes select datetime('now', '+1 day');
insert into datetimes select datetime('now', '+1 day');


create table unix(dtm int);

create index unix_idx on unix(dtm);

insert into unix select strftime('%s', 'now');
insert into unix select strftime('%s', 'now', '+1 day');
insert into unix select strftime('%s', 'now', '-1 day');

----

select * from dates 
where dte = date('now')
;

select dtm from datetimes
where dtm >= datetime(date('now'))
and dtm < datetime(date('now', '+1 day'))
;

select datetime(dtm, 'unixepoch', 'localtime') from unix
where dtm >= strftime('%s', date('now'))
and dtm < strftime('%s', date('now', '+1 day'))
;