Date Functions:
----------------------
1) Now() in Transaction Block don't change the time,it will show the same as transaction start time:
---------------
prod_db=# BEGIN ;
BEGIN
prod_db=#
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:12:24.323916-07
(1 row)
prod_db=# SELECT clock_timestamp();
clock_timestamp
-------------------------------
2014-09-15 15:43:11.407277-07
(1 row)
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:12:24.323916-07
(1 row)
prod_db=# END ;
COMMIT
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:43:28.662925-07
(1 row)
2) now() with interval usage :
---------------
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:12:24.323916-07
(1 row)
prod_db=# SELECT now()::date + interval '2 day';
?column?
---------------------
2014-09-17 00:00:00
(1 row)
3) Increasing interval timings :
--------------
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:45:17.948343-07
(1 row)
prod_db=# SELECT now() + interval '1day + 1 hour';
?column?
-------------------------------
2014-09-16 16:45:31.601508-07
(1 row)
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:45:57.193686-07
(1 row)
prod_db=# SELECT now() + interval '1day + 1 hour + 1 minute';
?column?
------------------------------
2014-09-16 16:46:59.13767-07
(1 row)
prod_db=# SELECT now() + interval '1day + 1 hour + 1 minute + 1 second';
?column?
------------------------------
2014-09-16 16:46:58.13767-07
(1 row)
3) Decreasing interval timings :
---------------------
prod_db=# SELECT now();
now
-------------------------------
2014-09-15 15:46:26.519146-07
(1 row)
prod_db=# SELECT now() - interval '1day + 1 hour + 1 minute';
?column?
-------------------------------
2014-09-14 14:45:35.910741-07
(1 row)
prod_db=# SELECT now() - interval '1day + 1 hour';
?column?
-------------------------------
2014-09-14 14:46:50.983887-07
(1 row)
prod_db=# SELECT now() - interval '1day';
?column?
-------------------------------
2014-09-14 15:46:57.448071-07
(1 row)
prod_db=# SELECT now() - interval '1day + 1 hour + 1 minute + 1 second';
?column?
-------------------------------
2014-09-14 14:46:21.792209-07
(1 row)
4) Extract:
----------
prod_db=# SELECT now();
now
-------------------------------
2014-09-16 04:46:30.926406-07
(1 row)
prod_db=# SELECT extract(DAY from now());
date_part
-----------
16
(1 row)
prod_db=# SELECT extract(DOW from now());
date_part
-----------
2
(1 row)
prod_db=# SELECT extract(DOY from now());
date_part
-----------
259
(1 row)
prod_db=# SELECT extract(MINUTE from now());
date_part
-----------
47
(1 row)
prod_db=# SELECT extract(MONTH from now());
date_part
-----------
9
(1 row)
prod_db=# SELECT extract(YEAR from now());
date_part
-----------
2014
(1 row)
prod_db=#
5) Time Zone Conversion :
-----------------------------
prod_db=# SELECT now();
now
-------------------------------
2014-09-16 04:52:15.909354-07
(1 row)
prod_db=# SELECT now() AT TIME ZONE 'GMT';
timezone
----------------------------
2014-09-16 11:52:28.293192
(1 row)
prod_db=# SELECT now() AT TIME ZONE 'IST';
timezone
----------------------------
2014-09-16 13:52:43.646523
(1 row)
prod_db=# SELECT now();
now
------------------------------
2014-09-16 04:53:03.48466-07
(1 row)
prod_db=# SELECT now() AT TIME ZONE 'PST';
timezone
----------------------------
2014-09-16 03:53:17.139931
(1 row)
prod_db=# SELECT now() AT TIME ZONE 'PDT';
timezone
----------------------------
2014-09-16 04:53:22.027907
(1 row)
prod_db=# SELECT now() AT TIME ZONE 'EDT';
timezone
----------------------------
2014-09-16 07:53:27.700054
(1 row)
prod_db=# SELECT extract(YEAR from created),extract(MONTH from created),extract(day from created),extract(hour from created),extract(minute from created),count(*) from tb_daily_overflow group by 1,2,3,4,5 order by 1,2,3,4,5;
date_part | date_part | date_part | date_part | date_part | count
-----------+-----------+-----------+-----------+-----------+-------
2014 | 9 | 15 | 10 | 20 | 2
2014 | 9 | 15 | 10 | 22 | 2
2014 | 9 | 15 | 14 | 55 | 1
2014 | 9 | 16 | 4 | 56 | 10
2014 | 9 | 16 | 5 | 56 | 10
2014 | 9 | 16 | 6 | 56 | 10
2014 | 9 | 16 | 7 | 2 | 10
2014 | 9 | 16 | 7 | 7 | 10
2014 | 9 | 16 | 10 | 15 | 1
2014 | 9 | 17 | 7 | 7 | 10
2014 | 9 | 18 | 7 | 7 | 10
2014 | 9 | 19 | 7 | 7 | 10
(12 rows)
6) Truncateing or Rounding Date functions:
-----------------------------------------------------
prod_db=# SELECT date_trunc('year', now());
date_trunc
------------------------
2014-01-01 00:00:00-08
(1 row)
prod_db=# SELECT date_trunc('month', now());
date_trunc
------------------------
2014-09-01 00:00:00-07
(1 row)
prod_db=# SELECT date_trunc('day', now());
date_trunc
------------------------
2014-09-16 00:00:00-07
(1 row)
prod_db=# SELECT date_trunc('hour', now());
date_trunc
------------------------
2014-09-16 05:00:00-07
(1 row)
prod_db=# SELECT date_trunc('minute', now());
date_trunc
------------------------
2014-09-16 05:08:00-07
(1 row)
prod_db=# SELECT date_trunc('second', now());
date_trunc
------------------------
2014-09-16 05:08:54-07
(1 row)
prod_db=# SELECT date_trunc('millisecond', now());
date_trunc
----------------------------
2014-09-16 05:09:03.884-07
(1 row)
prod_db=# SELECT date_trunc('microsecond', now());
date_trunc
-------------------------------
2014-09-16 05:09:25.020095-07
(1 row)
prod_db=#
prod_db=# SELECT date_trunc('day',created),date_trunc('hour',created),date_trunc('minute',created),date_trunc('second',created),count(*) from tb_daily_overflow group by 1,2,3,4 order by 1,2,3,4;
date_trunc | date_trunc | date_trunc | date_trunc | count
---------------------+---------------------+---------------------+---------------------+-------
2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:20:00 | 2014-09-15 10:20:55 | 1
2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:20:00 | 2014-09-15 10:20:58 | 1
2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:22:00 | 2014-09-15 10:22:03 | 1
2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:22:00 | 2014-09-15 10:22:05 | 1
2014-09-15 00:00:00 | 2014-09-15 14:00:00 | 2014-09-15 14:55:00 | 2014-09-15 14:55:41 | 1
2014-09-16 00:00:00 | 2014-09-16 04:00:00 | 2014-09-16 04:56:00 | 2014-09-16 04:56:32 | 10
2014-09-16 00:00:00 | 2014-09-16 05:00:00 | 2014-09-16 05:56:00 | 2014-09-16 05:56:51 | 10
2014-09-16 00:00:00 | 2014-09-16 06:00:00 | 2014-09-16 06:56:00 | 2014-09-16 06:56:54 | 10
2014-09-16 00:00:00 | 2014-09-16 07:00:00 | 2014-09-16 07:02:00 | 2014-09-16 07:02:05 | 10
2014-09-16 00:00:00 | 2014-09-16 07:00:00 | 2014-09-16 07:07:00 | 2014-09-16 07:07:11 | 10
2014-09-16 00:00:00 | 2014-09-16 10:00:00 | 2014-09-16 10:15:00 | 2014-09-16 10:15:47 | 1
2014-09-17 00:00:00 | 2014-09-17 07:00:00 | 2014-09-17 07:07:00 | 2014-09-17 07:07:23 | 10
2014-09-18 00:00:00 | 2014-09-18 07:00:00 | 2014-09-18 07:07:00 | 2014-09-18 07:07:29 | 10
2014-09-19 00:00:00 | 2014-09-19 07:00:00 | 2014-09-19 07:07:00 | 2014-09-19 07:07:34 | 10
(14 rows)
prod_db=#
7) Some miscellaneous functions:
postgres=# SELECT timeofday() as tofd; SELECT statement_timestamp() as st; SELECT now() as now; SELECT localtimestamp as lts; SELECT transaction_timestamp() as tt; SELECT localtime lt;
tofd
-------------------------------------
Tue Sep 16 05:22:11.492081 2014 PDT
(1 row)
st
-------------------------------
2014-09-16 05:22:11.492407-07
(1 row)
now
-------------------------------
2014-09-16 05:22:09.355997-07
(1 row)
lts
----------------------------
2014-09-16 05:22:09.355997
(1 row)
tt
-------------------------------
2014-09-16 05:22:09.355997-07
(1 row)
lt
-----------------
05:22:09.355997
(1 row)
postgres=#
postgres=#
postgres=# SELECT timeofday() as tofd; SELECT statement_timestamp() as st; SELECT now() as now; SELECT localtimestamp as lts; SELECT transaction_timestamp() as tt; SELECT localtime lt;
tofd
-------------------------------------
Tue Sep 16 05:22:17.556176 2014 PDT
(1 row)
st
-------------------------------
2014-09-16 05:22:17.556356-07
(1 row)
now
-------------------------------
2014-09-16 05:22:09.355997-07
(1 row)
lts
----------------------------
2014-09-16 05:22:09.355997
(1 row)
tt
-------------------------------
2014-09-16 05:22:09.355997-07
(1 row)
lt
-----------------
05:22:09.355997
(1 row)
