September 29, 2012 at 4:23 am
hello friends
I am working on a application where we are sending OTP as password to users but this OTP is active only for 2 hours. Now I want do some thing my database that update the status of OTP after 2 hours automaticaly according to it creation time. can any one help me?.
October 1, 2012 at 2:27 am
The only way that I can think of is to have an SQL Agent Job running on a schedule that updates the record that have expired. However unles you are running this every minute there will be a latency based on the schedule where some rows will not be expired until for the duration of the latency, so on a 10 minute schedule then you will have rows that may not expire for <expire duration> + Schedule, depending on when they were due to expire.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 1, 2012 at 3:45 am
Don't use "status." Use an expiry timestamp instead. Then use that when you query to see whether the entry has expired.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 1, 2012 at 3:52 am
dwain.c (10/1/2012)
Don't use "status." Use an expiry timestamp instead. Then use that when you query to see whether the entry has expired.
+1 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 1, 2012 at 4:19 am
nice once dwain, never thought of that, though I suppose it depends on how much you can change the underlying database structure.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 1, 2012 at 4:37 am
Jason-299789 (10/1/2012)
nice once dwain, never thought of that, though I suppose it depends on how much you can change the underlying database structure.
Actually I see this all the time. People create a field called status and then set it active/inactive. Without an "inactive" date, this is not a very extensible design because it means you can't mark the "thing" in the table as "to be deactivated on <some future date>."
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 1, 2012 at 5:53 am
If you want to get fancy-smancy, you could do it like this:
CREATE TABLE #Users
(UserID VARCHAR(20) NOT NULL
,Created_DT DATETIME DEFAULT (GETDATE())
,Expiry_DT DATETIME DEFAULT (DATEADD(hour, 2, GETDATE()))
,Is_Expired AS CASE WHEN GETDATE() > Expiry_DT THEN 'Y' ELSE 'N' END)
INSERT INTO #Users (UserID)
SELECT 'Dwain'
SELECT * FROM #Users
-- When User validates
UPDATE #Users SET Expiry_DT = '9999-12-31' WHERE UserID = 'Dwain'
SELECT * FROM #Users
DROP TABLE #Users
Now you have an Is_Expired flag you can query.
Edit: The only downside to this approach is you can't index on the computed Is_Expired column because it is not persisted, and you can't make it persisted (I believe) because it is non-deterministic.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply