[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Get one day old data from a relational table

Posted on 2011-02-25
22
Medium Priority
?
801 Views
Last Modified: 2012-05-11
I need to get yesterday's data from a table, which has a timestamp column. I use the follwing

Select * from Table where timestamp > dateadd(dd, -1, getdate())

But this gives me last 24 hrs data from the time it is run I would need to get just yesterday's data. How can I get that
0
Comment
Question by:gagangill
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34984972
Try: Select * from table where trunc(timestamp) = trunc(systimestamp-1);
0
 

Author Comment

by:gagangill
ID: 34985206
I tried that but I get invalid column name systimestamp

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985228
This was posted in an Oracle PL/SQL zone.

Are you using Oracle and if so, what version?

If you meant this for a different database let me know and I'll change the zone.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

Author Comment

by:gagangill
ID: 34985237
Also I am using Sybase
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985256
Now that I have it in the right Zone, I've never used Sybase but applying Oracle techniques to a quick Google, try this (no promises on performance based on how Oracle would handle this):

Select * from table
convert (smalldatetime, convert(varchar(10),dateadd(dd, -1, getdate()),101) ) =
convert (smalldatetime, convert(varchar(10),timestamp,101) )
0
 

Author Comment

by:gagangill
ID: 34985338
It looks like the performance will be an issue
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985397
Speaking only on Oracle: functions on dates in queries do not allow for index use.  If you cannot rewrite the SQL to use native date/timestamp values, you can create a Function Based Index.

If the query above doesn't perform and you have an index on the timestamp column, you'll need to figure out how to create a between query that will only return the correct day/time values.

pseudo-code:
Select * from Table where timestamp between <convertToTimestamp 02/24/2011 00:00:00> and
 <convertToTimestamp 02/24/2011 23:59:59>

Oracle has a few ways to do this but I cannot help in Sybase except to Google and guess a lot.

Hopefully a Sybase Expert will arrive sooon.
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 34985496
I think this will work.  If you don't want the three step statement, you can always add the convert that is assigned to @yesterday into the main select in the two places where @yesterday is used.

declare @yesterday varchar(10)
select @yesterday = convert(varchar(10), dateadd(dd, -1, getdate()), 111)
select * from Table where timestamp between convert(smalldatetime, @yesterday+" 00:00:00", 117) and convert(smalldatetime, @yesterday+" 23:59:59", 117)
0
 

Author Comment

by:gagangill
ID: 34985543
When I run the query I get an error

Syntax error during explicit conversion of varchar value '2011/02/24 23:49:59' to a smalldatetime function

0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 34985565
Apparently, it doesn't like 117 for smalldatetime.  Try using datetime for both conversions instead of smalldatetime.
0
 

Author Comment

by:gagangill
ID: 34985581
We get the same message for datetime too
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34985640
From some Google-ing try this:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug642.htm

Select * from Table where timestamp between dateadd(dd, -1, current_date()) and current_date()

be sure to test for rows that hit on the exact time changes.
0
 
LVL 27

Assisted Solution

by:wilcoxon
wilcoxon earned 500 total points
ID: 34985914
slightwy, that won't work - that picks up the last 24 hours - not all data for the previous day.

That's really weird - format 117 is output only - it can't be used to convert into a datetime.  Try this instead...

declare @yesterday varchar(10)
select @yesterday = convert(varchar(10), dateadd(dd, -1, getdate()), 107)
select * from Table where timestamp between convert(smalldatetime, @yesterday+" 00:00:00", 116) and convert(smalldatetime, @yesterday+" 23:59:59", 116)
0
 

Author Comment

by:gagangill
ID: 34987655
Thanks it works when I put 111 in the convert and use datetime and also I used single quote ' for 23:59:59 and 00:00:00
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35486634
I'm not sure what "PAQ'd" means for "closed as follows" so I hope I'm not objecting needlessly...

The author's final comment (34987655) says that my final suggestion (34985914) worked (with slight modification).  I would therefore recommend the question be closed awarding full points to me.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35487527
PAQ = Previously Asked Question.  It means 'accept' some answer.

Based on askers comment, I have no objection to accept:  http:#a34985914

>>slightwv, that won't work - that picks up the last 24 hours - not all data for the previous day.

Just curious (Oracle guy comparing to Sybase):  from what I read current_date() doesn't return a time portion.
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35488441
slightwy, I think I misread your post as getdate() when I made that comment.  That's really screwy - current_date() returns a full datetime (not just date) but it does appear to always have 12am time portion.  I'll have to remember current_date() as it will come in handy sometimes.


Recommendation: split points between wilcoxon (34985914) and slightwy (34985640).
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35488447
Thanks for the clarification and split!
0
 

Expert Comment

by:thermoduric
ID: 35702182
Forced accept implementing expert recommendations.

- thermoduric -
EE Community Support Moderator
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

A recent study by Google illustrates that almost 84% of patients go for both online and offline sources for hospital research. What do you think, “Is the Healthcare Industry the next to be disrupted by Digital Marketing?”
Data security in the cloud is very much like a security in an on-premises data center - only without costs for maintaining facilities and computer hardware.
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses

590 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