PostgreSQL how to concat interval value ‘2 days’

by

Part of the problem is that the standard SQL expression for intervals quotes the number, but not the keywords. So you have to be careful.

select current_date, current_date + interval '2' day;
--
2012-02-21   2012-02-23 00:00:00

In PostgreSQL, quoting like ‘2 day’ and ‘2 days’ also works. So you might think that ‘2’ || ‘ days’ would be equivalent, but it’s not.

select current_date, current_date + interval '2' || ' days';
--
2012-02-21   2012-02-21 00:00:02 days

The solution, as A.H. said, is to cast the result string as an interval.

You can also use a variable in place of 2. This generates a calendar for 2012.

-- 0 to 365 is 366 days; 2012 is a leap year.
select ('2012-01-01'::date + (n || ' days')::interval)::date calendar_date
from generate_series(0, 365) n;

I use that final cast to date, because date + interval returns a timestamp.

More Related Contents:

  1. Time zone names with identical properties yield different result when applied to timestamp
  2. How do I convert an interval into a number of hours with postgres?
  3. Insert, on duplicate update in PostgreSQL?
  4. How does the search_path influence identifier resolution and the “current schema”
  5. How do you find the row count for all your tables in Postgres
  6. PostgreSQL: running count of rows for a query ‘by minute’
  7. Run batch file with psql command without password
  8. invalid byte sequence for encoding “UTF8”
  9. Postgresql: Conditionally unique constraint
  10. How do you use variables in a simple PostgreSQL script?
  11. How to switch databases in psql?
  12. Cannot create a database table named ‘user’ in PostgreSQL
  13. Postgresql Truncation speed
  14. Buffers (Circle) in PostGIS
  15. How to customize the configuration file of the official PostgreSQL Docker image?
  16. Does a Postgres UNIQUE constraint imply an index?
  17. Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
  18. Dynamically generate columns for crosstab in PostgreSQL
  19. Postgresql insert trigger to set value
  20. postgres autoincrement not updated on explicit id inserts
  21. Retrieving Comments from a PostgreSQL DB
  22. Docker – How can run the psql command in the postgres container?
  23. Postgis installation: type “geometry” does not exist
  24. Displaying image in Ireports using PostgreSql
  25. Orbeon Forms Postgres DB connection
  26. Postgresql query between date ranges
  27. Postgres default sort by id – worldship
  28. Best way to install hstore on multiple schemas in a Postgres database?
  29. Loop through columns of RECORD
  30. How to drop PostgreSQL database through command line [closed]

Leave a Comment