[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

dynamic text for date interval increment

Posted on 2004-11-10
7
Medium Priority
?
4,187 Views
Last Modified: 2012-06-27
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.

0
Comment
Question by:meverest
  • 4
  • 3
7 Comments
 
LVL 3

Accepted Solution

by:
ctcampbell earned 2000 total points
ID: 12551441
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
 
LVL 3

Expert Comment

by:ctcampbell
ID: 12551530
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
 
LVL 37

Author Comment

by:meverest
ID: 12551743
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 3

Expert Comment

by:ctcampbell
ID: 12551859
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
 
LVL 37

Author Comment

by:meverest
ID: 12552049
>> 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
 
LVL 3

Expert Comment

by:ctcampbell
ID: 12552092
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
 
LVL 37

Author Comment

by:meverest
ID: 12552125
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question