How to subtract days to a Timestamp in CrateDB SQL query?

Question

How can i subtract days to a timestamp in CrateDB SQL query?

Exist something similar to this?

TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)

Answer 1

Don't think there is a built in function but you could do something like this

SELECT DATE_FORMAT(CURRENT_TIMESTAMP - 1000*60*60*24*14) LIMIT 100

in this example (1000 * 60 * 60) * 24 * 14 (24 is to get days and 14 is your number of days)

NB. You can also cast dates into timestamp and perform similar functionality

SELECT  ABS(cast('2019-01-1' AS TIMESTAMP) - CURRENT_TIMESTAMP ) / (1000*60*60*24) LIMIT 100

this will get you a number of days between now and 1st of January

So far that's all what they have in their docs

Answer 2

You can subtract INTERVAL from TIMESTAMP, but before any matematichal operation you need to CAST the datatype, you can do it in this way:

SELECT now() - CAST('14 day' AS INTERVAL)

Or the same function of above, but in a contracted way

SELECT now() - '14 day'::INTERVAL;

As a string to be CAST to an INTERVAL you can use a number followed by any of this:

• second
• minute
• hour
• day
• week
• month
• quarter
• year