Postgresql Date

Working with BIGint to TIMESTAMP, and TIMESTAMP to BIGINT for POSTGRESQL.

Login to Postgreql and Create TABLE

root@ubuntu:/home/sophal/system/escwork/class# su - postgres
postgres@ubuntu:~$ psql test2;
psql (8.4.10)
Type "help" for help.

test2=# CREATE TABLE mytesttime(
test2(# id INT NOT NULL PRIMARY KEY,
test2(# name VARCHAR(200) NOT NULL,
test2(# time BIGINT NOT NULL);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytesttime_pkey" for table "mytesttime"
CREATE TABLE

Cast CURRENT_TIMESTAMP to BIGINT

extract('epoch' FROM CURRENT_TIMESTAMP)::bigint

Insert Some Record

test2=# INSERT INTO mytesttime VALUES(1,'King',extract('epoch' FROM CURRENT_TIMESTAMP)::bigint),
test2-# (2,'Queen',extract('epoch' FROM CURRENT_TIMESTAMP)::bigint),
test2-# (3,'Boy',extract('epoch' FROM CURRENT_TIMESTAMP)::bigint);
INSERT 0 3
test2=# SELECT * FROM mytesttime;
 id | name  |    time    
----+-------+------------
  1 | King  | 1327692853
  2 | Queen | 1327692853
  3 | Boy   | 1327692853
(3 rows)

Convert BIGINT to Timestamp format

time is the columns name. You can replace time with your column name that have the data type of bigint.

'1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval

Select To make the time to have the datetime format

test2=# SELECT id,name, 
 '1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval as time2 
FROM mytesttime;
 id | name  |        time2        
----+-------+---------------------
  1 | King  | 2012-01-27 19:34:13
  2 | Queen | 2012-01-27 19:34:13
  3 | Boy   | 2012-01-27 19:34:13

Query To get Date ONLY

test2=# SELECT id,name, 
 ('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::date as time2 
FROM mytesttime;
 id | name  |   time2    
----+-------+------------
  1 | King  | 2012-01-27
  2 | Queen | 2012-01-27
  3 | Boy   | 2012-01-27
(3 rows)

Query To get Time ONLY

test2=# SELECT id,name, 
 ('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::time as time2 
FROM mytesttime;
 id | name  |  time2   
----+-------+----------
  1 | King  | 19:34:13
  2 | Queen | 19:34:13
  3 | Boy   | 19:34:13
(3 rows)

Insert A few more records

test2=# INSERT INTO mytesttime VALUES(4,'Lilo',extract('epoch' FROM CURRENT_TIMESTAMP)::bigint),
(6,'Queen Elizabet',extract('epoch' FROM CURRENT_TIMESTAMP)::bigint),
(5,'Bigboy',extract('epoch' FROM CURRENT_TIMESTAMP)::bigint);
INSERT 0 3
test2=# SELECT id,name,                                                                         
 ('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::date as time2 
FROM mytesttime;
 id |      name      |   time2    
----+----------------+------------
  1 | King           | 2012-01-27
  2 | Queen          | 2012-01-27
  3 | Boy            | 2012-01-27
  4 | Lilo           | 2012-01-27
  6 | Queen Elizabet | 2012-01-27
  5 | Bigboy         | 2012-01-27
(6 rows)

Select and display by time

test2=# SELECT id,name, 
 ('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::time as time2 
FROM mytesttime;
 id |      name      |  time2   
----+----------------+----------
  1 | King           | 19:34:13
  2 | Queen          | 19:34:13
  3 | Boy            | 19:34:13
  4 | Lilo           | 20:30:24
  6 | Queen Elizabet | 20:30:24
  5 | Bigboy         | 20:30:24
(6 rows)

Aggregate Function Count

test2=# SELECT COUNT(id) as Total,
test2-# ('1970-01-01 00:00:00 GMT'::timestamp + ((time::text)::interval))::time 
test2-# as Mytime
test2-# FROM mytesttime GROUP BY Mytime;
 total |  mytime  
-------+----------
     3 | 19:34:13
     3 | 20:30:24
(2 rows)

Aggregate Function Group By Date

test2=# SELECT COUNT(id) as Total,
('1970-01-01 00:00:00 GMT'::timestamp + ((time::text)::interval))::date 
as MyDate
FROM mytesttime GROUP BY MyDate;
 total |   mydate   
-------+------------
     6 | 2012-01-27
(1 row)