[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Get one day old data from a relational table

Posted on 2011-02-25
22
Medium Priority
?
796 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +1
22 Comments
 
LVL 77

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 77

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

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

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 77

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 77

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 77

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 77

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
Here in this article, you will get a step by step guidance on how to restore an Exchange database to a recovery database. Get a brief on Recovery Database and how it can be used to restore Exchange database in this section!
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

649 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