dsaiztc
9/27/2017 - 10:24 AM

postgres.sql

seconds_from_interval.sql
content_copyfile_download
-- Get total seconds in '1 day 15:11:10.123'
SELECT EXTRACT(EPOCH FROM '1 day 15:11:10.123'::INTERVAL)
# 141070.123
postgresql_date_trunc.md
content_copyfile_download
  • Rendered
  • Source

Select part of a timestamp:

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

Trunc part of the timestamp:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
last_week.sql
content_copyfile_download
/* https://stackoverflow.com/a/30783772/3149679 */

WHERE
  created >= date_trunc('week', CURRENT_TIMESTAMP - interval '1 week') 
  AND
  created < date_trunc('week', CURRENT_TIMESTAMP)