Postgresql date comparison

If the table store date using BIGINT, it is a little trick to do the date comparison directly. I have worked with date comparison within the Postgresql for a while, and I discovered the easy way that make the date comparison a lot easier.

mdl_log table

totaradb3=# \d mdl_log
                                 Table "public.mdl_log"
 Column |          Type          |                      Modifiers                       
--------+------------------------+------------------------------------------------------
 id     | bigint                 | not null default nextval('mdl_log_id_seq'::regclass)
 time   | bigint                 | not null default 0
 userid | bigint                 | not null default 0
 ip     | character varying(15)  | not null default ''::character varying
 course | bigint                 | not null default 0
 module | character varying(20)  | not null default ''::character varying
 cmid   | bigint                 | not null default 0
 action | character varying(40)  | not null default ''::character varying
 url    | character varying(100) | not null default ''::character varying
 info   | character varying(255) | not null default ''::character varying
Indexes:
    "mdl_log_id_pk" PRIMARY KEY, btree (id)
    "mdl_log_act_ix" btree (action)
    "mdl_log_cmi_ix" btree (cmid)
    "mdl_log_coumodact_ix" btree (course, module, action)
    "mdl_log_tim_ix" btree ("time")
    "mdl_log_usecou_ix" btree (userid, course)

Create View

I create the view table so that we have the date column to use for comparing the date.

CREATE VIEW mylogs AS 
  SELECT *,('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::date as date
      FROM mdl_logs ;

select view

totaradb3=# SELECT date FROM mylogs LIMIT 10;
    date    
------------
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
 2011-12-17
(10 rows)

Get all users in a specific date

totaradb3=# SELECT COUNT(userid) AS total FROM mylogs WHERE date='2011-08-22';
 total 
-------
  8438
(1 row)

get all users in a date range

totaradb3=# SELECT COUNT(userid) AS total FROM mylogs WHERE date <= '2011-08-22' AND date <= '2011-10-18';
 total 
-------
 68397
(1 row)

get certificate received

SELECT COUNT(userid) as total FROM mylogs
WHERE date='2011-08-22' AND
      module = 'certificate' 
      AND
      action='received';

get all certificate activities

SELECT COUNT(userid) AS total FROM mylogs
WHERE 
       date='2011-08-22' AND
       module='certificate'

get all certificate views

SELECT COUNT(userid) AS total FROM mylogs
WHERE 
       date='2011-08-22' AND
       module='certificate' AND
       action ='view'

get all certificate update

SELECT COUNT(userid) AS total FROM mylogs
WHERE 
       date='2011-08-22' AND
       module='certificate' AND
       action ='update'

get all certificate add

SELECT COUNT(userid) AS total FROM mylogs
WHERE 
       date='2011-08-22' AND
       module='certificate' AND
       action ='add'