dynamic text for date interval increment

Hi Folks,

I want to read a value dynamically from a db table to update a timestamp value like:

update users set expirydate = now() + interval '1 month' where username = 'bob'

but instead of the text '1 month', i want to get the text fom a table row:

update
    users
set
    users.expirydate = now() + interval users.interval,
    users.interval = null
where
    users.username = 'bob' and
    interval is not null

However if i use the syntax above, the sql interpreter does not seem to be able to make the substitution.

Any tips?

Thanks and regards,  Mike.

LVL 37
meverestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ctcampbellCommented:
Since UPDATE only operates on a single table (specified by UPDATE "tablename"), you should not include the table name (or alias) in your update column list.  Otherwise, your idea is right, just the syntax is wrong.  Try this:

--- Begin SQL code ---
update
    users
set
    --! this is the update column list
    expirydate = now() + interval::interval,
    interval = null
    --!
where
    users.username = 'bob' and
    interval is not null
;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ctcampbellCommented:
BTW, although PostgreSQL will allow you to create a column with the same name as a built-in data type (i.e. "interval"), I would highly recommend NOT doing so, as it makes your code less readable and may cuase problems if you aren't quoting your identifier.  "interval::interval" will work, but "expiryinterval::interval" (or the SQL standard "CAST(expiryinterval AS interval)") is a little more descriptive.
0
meverestAuthor Commented:
thanks ctcampbell,

yes, i used the table namkes just as a way to clarify what i was trying to do - the column name i was using to store the interval string didn't make much sense when i wrote it in - i didn't actually specify them in the real query.

it turned out that my problem was about trying to use a varchar type and cast it to interval data - nothing i could do would make it work as the interpreter either thought that the row.  coming from MSSQL background, i had no idea of interval datatype, and so i didn't even consider storing it in an INTERVAL datatype column - i was storing it as text instead! <duh>

so as soon as i worked that out, got it going smoothly.

thanks for the response,

Regards,  Mike.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ctcampbellCommented:
Well storing as a text field and casting worked, though.

I just recently started using PostgreSQL after 5 years with SQL Server, so I know how you feel.  The INTERVAL type is pretty neat.  Not to mention real DATE, TIME, and TIMESTAMP WITH TIME ZONE types.  Also, I have found date_trunc() really nice compared to doing "CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + CAST(MONTH(GETDATE()) AS CHAR(2)) + '01' AS DATETIME)" to get the first of the month.
0
meverestAuthor Commented:
>> Well storing as a text field and casting worked, though.

is that right?  i can't imagine what i was doing wrong then....

>> I just recently started using PostgreSQL after 5 years with SQL Server, so I know how you feel.

too right, sure is a tad unfamiliar at first, but man, postgres rocks! :-)

cheers!
 
0
ctcampbellCommented:
Did you try the snippet I posted?  It worked for me.  I am using 8.0.0-beta4 on Windows, though I don't think that should make any difference.  FWIW, using an INTERVAL is the better route, but what you were doing should work, and it leaves the interval in a readable form for the user.  Do you want to display a 7200 second interval as 120 minutes or 2 hours?  If you leave it as text, you already know :)

To make absolutely sure the server knows exactly what you want, I would quote the field (if it is a reserved word) and use CAST instead of ::.

--- Begin SQL code ---
update
    users
set
    --! this is the update column list
    expirydate = now() + cast("interval" as interval),
    interval = null
    --!
where
    users.username = 'bob' and
    interval is not null
;
0
meverestAuthor Commented:
G'day,

no i hadn't tried your snipp, as i had already solved it by the time i came back to see your response.

>> Do you want to display a 7200 second interval as 120 minutes or 2 hours?

looking at the data in pgadminIII, the data is converted to readable form, eg "24 hour 2 min 5 sec"

it comes out of the odbc interface the same way which makes it easy to parse and display (using coldfusion)

for what it's worth, this is what i ended up with:

update
      radlogin
set
      expiryadvance = now() + duration,
      duration = null,
      expiryadvanceevent = null
where
      username = new.username and
      expiryadvance is not null and
      expiryadvanceevent = 'nextlogon'

it's a trigger function on freeradius accounting table to update expiry on pre-pay wireless hotspot tickets on first access.

works very nicely.

cheers!


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.