Solved

Get one day old data from a relational table

Posted on 2011-02-25
22
776 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 76

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 76

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
 

Author Comment

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

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 76

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 76

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 76

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 76

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now