PostgreSQL - subtract 'days' from a returned 'date' value without also returning timestamp

Question

I'm querying a table to get some date, like so:

SELECT date - INTERVAL '10 day' AS date
FROM example_table
WHERE username = 'Bob'
LIMIT 1;

The date column in the example_table does not have a timestamp. All dates in the column are stored in the following manner:

YYYY-MM-DD

The query above will return a result like so:

2016-11-20 00:00:00.000000

It takes the date found, goes back 10 days, and returns that date. But I want it to return the date without adding the timestamp, like so:

2016-11-20

If I use INTERVAL it always seems to add a timestamp. Is there a way to only get the date?

date is a really bad choice for a column name because it's also a keyword. Plus it doesn't document what kind of "date" that is. The "due date"? The "birth date"? The "registration date"? The "expiration date"? — user330315
– user330315, Commented Oct 28, 2019 at 14:50

GMB · Accepted Answer · 2019-10-28 18:10:38Z

2

Your query is fine (but can be simplified, as demonstrated by a_horse_with_no_name). What you are seeing is a display issue. You can format your date to a string in the relevant format using to_char():

SELECT to_char("date" - INTERVAL '10 day', 'yyyy-mm-dd') AS "date"
FROM example_table
WHERE username = 'Bob'
LIMIT 1;

Note: LIMIT without an ORDER BY does not make sense: if there is more than one record in the resultset, you actually get a random record out of them.

edited Oct 28, 2019 at 18:10

answered Oct 28, 2019 at 14:59

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-10-28 18:16:00Z

1

You can use the interval notation and convert back to a date:

SELECT (date - INTERVAL '10 day')::date AS date

answered Oct 28, 2019 at 18:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

user330315 · Accepted Answer · 2019-10-28 14:49:15Z

0

You can subtract (or add) an integer from a date. That integer represents the number of days:

SELECT "date" - 10 AS "date"
FROM example_table
WHERE username = 'Bob'
LIMIT 1;

answered Oct 28, 2019 at 14:49

user330315

Collectives™ on Stack Overflow

PostgreSQL - subtract 'days' from a returned 'date' value without also returning timestamp

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related