CODEWITHSUNDEEP
sqlpostgresqlepoch
Blackdynomite
Blackdynomite

Reputation: 431

Subtracting Day from Postgresql EPOCH Column

I have the following code where I am trying to simply subtract 5 days from the date. The date is stored as in EPOCH time (miliseconds, 13 numbers)in the t.Date_created field. but for some reason the code does not work with the following error. Any advice would be helpful!!!

[42883] ERROR: operator does not exist: timestamp with time zone - integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Code below:

SELECT to_timestamp(t.date_Created / 1000) - 5 FROM task_mgmt.teams t LIMIT 5;

Upvotes: 0

Views: 1120

Answers (2)

user330315
user330315

Reputation:

You need to subtract an interval

to_timestamp(t.date_Created / 1000) - interval '5 days'

Integers can only be subtracted directly from a date value, not from a timestamp value.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270793

I would suggest arithmetic:

t.date_created - 5 * 24 * 60 * 60 * 1000

Upvotes: 0

Related Questions