?
Solved

Get one day old data from a relational table

Posted on 2011-02-25
22
Medium Priority
?
793 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

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 26

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 26

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 26

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 26

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 26

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

777 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