sql - Can i read interval value from another column in database -


i have table (custid,purchase_timestamp (dd-mm-yyyy), warranty_period) in days. need sum of purchase_time , warranty_period.how can write sql this.

i tried 

select date_sub(purchase_timestamp, interval warranty_period day);

but not work. please suggest

 |custid |    warranty_period  |   purchase_timestamp     |1        |      365            |   03-01-2017       |2        |      30             |   03-04-2017     |3        |     10              |   25-05-2017     |4        |      30             |   20-05-2017     |5        |     365             |   04-06-2017     |6        |     100             |   18-06-2017     |7        |     90              |   30-06-2017     |8        |     10              |   05-07-2017     |9        |        30           |   09-07-2017     |10       |     365             |   17-07-2017

let's assume table:

create table t (     custid integer,     warranty_period integer,     purchase_timestamp date /* timestamp ? */ );

with data:

insert t     (custid, warranty_period, purchase_timestamp) values     ( 1, 365, '03-01-2017'),       ( 2,  30, '03-04-2017'),     ( 3,  10, '25-05-2017'),     ( 4,  30, '20-05-2017'),     ( 5, 365, '04-06-2017'),     ( 6, 100, '18-06-2017'),     ( 7,  90, '30-06-2017'),     ( 8,  10, '05-07-2017'),     ( 9,  30, '09-07-2017'),     (10, 365, '17-07-2017') ;

you can use following select:

select      custid,     purchase_timestamp + cast(warranty_period || ' days' interval) end_of_warranty      t  order     custid ;

or

select      custid,     purchase_timestamp + make_interval(days := warranty_period) end_of_warranty      t  order     custid ;

and get

 custid | end_of_warranty     -----: | :------------------      1 | 2018-01-03 00:00:00      2 | 2017-05-03 00:00:00      3 | 2017-06-04 00:00:00      4 | 2017-06-19 00:00:00      5 | 2018-06-04 00:00:00      6 | 2017-09-26 00:00:00      7 | 2017-09-28 00:00:00      8 | 2017-07-15 00:00:00      9 | 2017-08-08 00:00:00     10 | 2018-07-17 00:00:00

note postgresql know how add interval date (or timestamp), , return timestamp.

to specify interval need use 

cast(warranty_period || ' days' interval)

or

warranty_period * interval '1 day'

which close sql standard, or use

make_interval(days := warranty_period)

that uses specific postgresql date/time function.

you can use (simplest) form:

select      custid,     purchase_timestamp + warranty_period end_of_warranty      t  order     custid ;

that relies on fact there + operator (date + integer) treats integer number of days, , perform same operation. in case, column purchase_timestamp should actually date, or cast(... date).

check out options @ dbfiddle here


Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more