I have a Datetime field that I need to relate with another field but the
seconds portion is messing me up. What function can I use to strip out (or
reset to 00) the seconds part of a date/time?
I think I may have found something but it seems to being taking forever to
query.
I used the following code to strip out the seconds
DateAdd("s",-(DatePart("s",[DATETIME])),[DATETIME])
Is this something that would slow down my query drastically?
dandc0711 said:I think I may have found something but it seems to being taking forever to
query.
I used the following code to strip out the seconds
DateAdd("s",-(DatePart("s",[DATETIME])),[DATETIME])
Is this something that would slow down my query drastically?
dandc0711 said:Wont that strip off the number seconds in the time "right now" ? These
Date/Times are timestamps from the past and iterated so for example:
DATETIME
1/4/2010 3:57:13 AM
1/4/2010 3:58:13 AM
1/4/2010 3:59:13 AM
1/4/2010 4:00:13 AM
1/4/2010 4:01:13 AM
1/4/2010 4:02:13 AM
1/4/2010 4:03:14 AM
1/4/2010 4:04:14 AM
1/4/2010 4:05:14 AM
1/4/2010 4:06:14 AM
1/4/2010 4:07:14 AM
1/4/2010 4:08:14 AM
So you see that the seconds iterate at a fairly random interval and because
of this they never seem to match up quite right. I don't need the detail of
matching by seconds, only minutes. So I'm trying to remove whatever are the
excess seconds so that I can create a relationship between this and another
identically formatted field.
dandc0711 said:I think I may have found something but it seems to being taking forever to
query.
I used the following code to strip out the seconds
DateAdd("s",-(DatePart("s",[DATETIME])),[DATETIME])
Is this something that would slow down my query drastically?
dandc0711 said:Wont that strip off the number seconds in the time "right now" ? These
Date/Times are timestamps from the past and iterated so for example:
DATETIME
1/4/2010 3:57:13 AM
1/4/2010 3:58:13 AM
1/4/2010 3:59:13 AM
1/4/2010 4:00:13 AM
1/4/2010 4:01:13 AM
1/4/2010 4:02:13 AM
1/4/2010 4:03:14 AM
1/4/2010 4:04:14 AM
1/4/2010 4:05:14 AM
1/4/2010 4:06:14 AM
1/4/2010 4:07:14 AM
1/4/2010 4:08:14 AM
So you see that the seconds iterate at a fairly random interval and because
of this they never seem to match up quite right. I don't need the detail of
matching by seconds, only minutes. So I'm trying to remove whatever are the
excess seconds so that I can create a relationship between this and another
identically formatted field.
Hi John,
Umm... That strips out the entire time, not just the seconds.
Clifford Bass
John said:Try
DateValue([YourField])
Or
CDate(Int([YourField]))
Those will both fail if YourField is null, so you may want to check for that
before attempting the conversion. Or optionally using a "dummy" date such as:
DateValue(NZ([YourField],#1800-1-1#))
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Clifford Bass via AccessMonster.com said:Hi,
How about you tell us your goal here? And how many records are in your
table? It looks like you may be using a linked table to a non-access back
end. Is that the case? If so, what is the back end database brand? Any
other pertinant information?
Clifford Bass
Ok so my last query never finished running so its a failure. I Then got the
idea of using a subquery rather than a join in hopes of speeding it up but I
can't get my subquery to return any results.
I need to basically create a subquery that returns a single result Where the
Datestamp of the returned record is equal to one minute later than the
datestamp in the current record.
My first blush SQL is as follows but its flawed because the Where clause in
the subquery is circular so it doesn't return anything.
SELECT dbo001.Datestamp, dbo001.FILENAME, dbo001.PROCESS, (SELECT [PROCESS]
FROM dbo001 WHERE [Datestamp] = (DateAdd("n",1,[Datestamp]))) AS
PROCESS_FROM_NEXT_RECORD
FROM dbo001
ORDER BY dbo001.Datestamp;
--
Message posted via AccessMonster.com
.
Clifford Bass via AccessMonster.com said:Hi,
Okay, that clarifies the issue and why you are getting such poor
performance. Access has to do all those calculations on all of the records,
maybe multiple times. It is better in this situation to avoid the use the
Access's functions so that the back end server can do all of the work. Try
this:
SELECT A.DATETIME, A.FILENAME, A.PROCESS
FROM dbo001 AS A
WHERE ((((select PROCESS from dbo001 as B where B.DATETIME = (select Max
(DATETIME) from dbo001 as C where C.DATETIME < A.DATETIME))) Is Null)) OR (((
(select PROCESS from dbo001 as B where B.DATETIME = (select Max(DATETIME)
from dbo001 as C where C.DATETIME < A.DATETIME)))<>[A].[PROCESS])) OR (((
(select PROCESS from dbo001 as D where D.DATETIME = (select Min(DATETIME)
from dbo001 as E where E.DATETIME > A.DATETIME)))<>[A].[PROCESS])) OR (((
(select PROCESS from dbo001 as D where D.DATETIME = (select Min(DATETIME)
from dbo001 as E where E.DATETIME > A.DATETIME))) Is Null));
If it does not run fast using the linked table, you might try it as a
pass-through query.
Clifford Bass
Ok,
The underlying issue that I'm attempting to resolve here is as follows. I
have a very large linked table (on the order of 200k records) currently
housed on a SQL based server (I'm not sure the brand). This table has many
columns but I'm only really interested in 3, [Filename], [Datetime],
[Process]. The server's data is generated by a Crystal growing machine that
every minute (give or take a second here and there, thus my problem) spits
out a data record with all of the crystals immediate measurements. These
records are collected into an every growing table on the server to which I am
linked. Since I control neither the Growing machine nor the server on which
the data is stored I can't alter it all or update how it's stored.
So theres the background, it produces data such as the following:
DATETIME FILENAME PROCESS
1/12/2010 10:49:16 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:50:17 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:51:17 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:52:17 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:52:47 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:53:17 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:53:47 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:54:17 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:54:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:55:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:55:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:56:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:56:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:57:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:57:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:58:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:58:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:59:48 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:00:48 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:01:49 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:02:49 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:03:49 PM 20100112_SA0110005B.csv MELT
Take this and extrapolate it to hundreds of records. I am responsible for
going into these tables and extracting 3 very specific Datetimes from each
FILENAME series. I only care about those datetimes immediatly preceding and
following a change in process so In an effort to make this easier I want to
not display all the records that fill out each process. (So show the first
time Vacuum Appears, then show the last vacuum before it changes to
LEAK_CHECK but don't display all the ones in between, then show the first
LEAK_CHECK etc..) I can't just group them because sometimes processes repeat
(i.e. It can go: Vacuum, Leak Check, Melt, Neck, Vacuum, Leak check etc...)
and I need to be able to capture every instance of that change happening.
I was attempting to do this by creating 2 identical copies of the recordset
and joining them 1 off from each other so on each row I can compare the
Process with the Next Process and check if they are identical and if so don't
display that record.
--
Message posted via AccessMonster.com
.
Clifford Bass via AccessMonster.com said:Hi,
Okay, that clarifies the issue and why you are getting such poor
performance. Access has to do all those calculations on all of the records,
maybe multiple times. It is better in this situation to avoid the use the
Access's functions so that the back end server can do all of the work. Try
this:
SELECT A.DATETIME, A.FILENAME, A.PROCESS
FROM dbo001 AS A
WHERE ((((select PROCESS from dbo001 as B where B.DATETIME = (select Max
(DATETIME) from dbo001 as C where C.DATETIME < A.DATETIME))) Is Null)) OR (((
(select PROCESS from dbo001 as B where B.DATETIME = (select Max(DATETIME)
from dbo001 as C where C.DATETIME < A.DATETIME)))<>[A].[PROCESS])) OR (((
(select PROCESS from dbo001 as D where D.DATETIME = (select Min(DATETIME)
from dbo001 as E where E.DATETIME > A.DATETIME)))<>[A].[PROCESS])) OR (((
(select PROCESS from dbo001 as D where D.DATETIME = (select Min(DATETIME)
from dbo001 as E where E.DATETIME > A.DATETIME))) Is Null));
If it does not run fast using the linked table, you might try it as a
pass-through query.
Clifford Bass
Ok,
The underlying issue that I'm attempting to resolve here is as follows. I
have a very large linked table (on the order of 200k records) currently
housed on a SQL based server (I'm not sure the brand). This table has many
columns but I'm only really interested in 3, [Filename], [Datetime],
[Process]. The server's data is generated by a Crystal growing machine that
every minute (give or take a second here and there, thus my problem) spits
out a data record with all of the crystals immediate measurements. These
records are collected into an every growing table on the server to which I am
linked. Since I control neither the Growing machine nor the server on which
the data is stored I can't alter it all or update how it's stored.
So theres the background, it produces data such as the following:
DATETIME FILENAME PROCESS
1/12/2010 10:49:16 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:50:17 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:51:17 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:52:17 PM 20100112_SA0110005B.csv VACUUM
1/12/2010 10:52:47 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:53:17 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:53:47 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:54:17 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:54:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:55:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:55:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:56:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:56:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:57:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:57:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:58:18 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:58:48 PM 20100112_SA0110005B.csv LEAK_CHECK
1/12/2010 10:59:48 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:00:48 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:01:49 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:02:49 PM 20100112_SA0110005B.csv MELT
1/12/2010 11:03:49 PM 20100112_SA0110005B.csv MELT
Take this and extrapolate it to hundreds of records. I am responsible for
going into these tables and extracting 3 very specific Datetimes from each
FILENAME series. I only care about those datetimes immediatly preceding and
following a change in process so In an effort to make this easier I want to
not display all the records that fill out each process. (So show the first
time Vacuum Appears, then show the last vacuum before it changes to
LEAK_CHECK but don't display all the ones in between, then show the first
LEAK_CHECK etc..) I can't just group them because sometimes processes repeat
(i.e. It can go: Vacuum, Leak Check, Melt, Neck, Vacuum, Leak check etc...)
and I need to be able to capture every instance of that change happening.
I was attempting to do this by creating 2 identical copies of the recordset
and joining them 1 off from each other so on each row I can compare the
Process with the Next Process and check if they are identical and if so don't
display that record.
--
Message posted via AccessMonster.com
.