Menu
I have a table of items with the following columns:
start_time column (timestamp without time zone)expiration_time_seconds column (integer)For example, some values are:
SELECT start_time, expiration_time_seconds
FROM whatever
ORDER BY start_time;
start_time | expiration_time_seconds
----------------------------+-------------------------
2014-08-05 08:23:32.428452 | 172800
2014-08-10 09:49:51.082456 | 3600
2014-08-13 13:03:56.980073 | 3600
2014-08-21 06:31:38.596451 | 3600
...
How do I add the expiration time, given in seconds, to the start_time?
I have tried to format a time interval string for the interval command, but failed:
blah=> SELECT interval concat(to_char(3600, '9999'), ' seconds');
ERROR: syntax error at or near "("
LINE 1: SELECT interval concat(to_char(3600, '9999'), ' seconds');
577
If you need to add a time interval to a timestamp or date data, using a variable, then you will have only to multiply the variable (that is, a number of unities) by the correct interval , and use the appropriate operator (+/-). The result will be a timestamp , as you can see on the documentation.
In PostgreSQL, the make_interval() function creates an interval from years, months, weeks, days, hours, minutes and seconds fields. You provide the years, months, weeks, days, hours, minutes and/or seconds fields, and it will return an interval in the interval data type.
Postgres DATE data type Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
The trick is to create a fixed interval and multiply it with the number of seconds in the column:
SELECT start_time,
expiration_time_seconds,
start_time + expiration_time_seconds * interval '1 second'
FROM whatever
ORDER BY start_time;
start_time | expiration_time_seconds | end_time
----------------------------|-------------------------|----------------------------
2014-08-05 08:23:32.428452 | 172800 | 2014-08-07 08:23:32.428452
2014-08-10 09:49:51.082456 | 3600 | 2014-08-10 10:49:51.082456
2014-08-13 13:03:56.980073 | 3600 | 2014-08-13 14:03:56.980073
2014-08-21 06:31:38.596451 | 3600 | 2014-08-21 07:31:38.596451
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With