Solved

Get one day old data from a relational table

Posted on 2011-02-25
22
782 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
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 125 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 125 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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