Unix time to 15 minute increments

[wifor]

Greetings all,

Thanks, ahead of time for any and all assistance or thought given to this
issue. I am at a call center and my question is how do I break down
statistics into 15 minutes increments? The time is initially gathered in
'Unix time'. I convert and adjust it to military time but am having a hard
time dividing it into every 15 minute increments. example: 0800 - 0815,
0816 - 0830, 0831 - 0845, 0846- 0859

Script:
SELECT
dbo_CDRMAIN.LocalDay,
(dbo_CDRMAIN.StartTime)-(dbo_cdrmain.GMTOffset) AS Adj_Unix_Time,
Format(([Adj_Unix_Time]/86400),"hh:nn:ss") AS Real_Time,

FROM
dbo_CDRMAIN

WHERE
(((dbo_CDRMAIN.LocalDay) Between 20080710 And 20080716) AND ((dbo_CDRMAIN.
TargetWGNum) In (5000,5001,5002,5600,5601,5602)))

ORDER BY dbo_CDRMAIN.LocalDay, dbo_CDRMAIN.StartTime;

Results:
LocalDay Adj_Unix_Time Real_Time
20080716 1216217982 14:19:42
20080716 1216218273 14:24:33
20080716 1216218375 14:26:15
20080716 1216218591 14:29:51
20080716 1216218596 14:29:56
20080716 1216218605 14:30:05
20080716 1216218623 14:30:23
20080716 1216218686 14:31:26
20080716 1216218693 14:31:33
20080716 1216218718 14:31:58
20080716 1216218725 14:32:05
20080716 1216218754 14:32:34
20080716 1216218766 14:32:46


Thanks, Wifor

 

[John Spencer]

I haven't come up with anything simpler than
CDate((((TimeValue(CDate([Adj_Unix_Time]/86400))*86400)\900)*900)/86400)

There has to be a better way to do that, but I'm not seeing it right now.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try using Partition function. Partition(number, start, stop, interval)
The 'number' is your date. Start = 0 Stop = 59
The inverval would be 15

SELECT Format([Date open],"hh") AS [Hour of day], Partition(Format([Date
open],"n"),0,59,15) AS [Count-per-interval], Count([Change Requests].[Date
open]) AS [CountOfDate open]
FROM [Change Requests]
WHERE ((([Change Requests].[Date open]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([Date open],"hh"), Partition(Format([Date open],"n"),0,59,15);

 

[KARL DEWEY]

Better make the stop 60 as some could have 59 minutes and odd seconds.
--
KARL DEWEY
Build a little - Test a little

Try using Partition function. Partition(number, start, stop, interval)
The 'number' is your date. Start = 0 Stop = 59
The inverval would be 15

SELECT Format([Date open],"hh") AS [Hour of day], Partition(Format([Date
open],"n"),0,59,15) AS [Count-per-interval], Count([Change Requests].[Date
open]) AS [CountOfDate open]
FROM [Change Requests]
WHERE ((([Change Requests].[Date open]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([Date open],"hh"), Partition(Format([Date open],"n"),0,59,15);

--
KARL DEWEY
Build a little - Test a little

Greetings all,

Thanks, ahead of time for any and all assistance or thought given to this
issue. I am at a call center and my question is how do I break down
statistics into 15 minutes increments? The time is initially gathered in
'Unix time'. I convert and adjust it to military time but am having a hard
time dividing it into every 15 minute increments. example: 0800 - 0815,
0816 - 0830, 0831 - 0845, 0846- 0859

Script:
SELECT
dbo_CDRMAIN.LocalDay,
(dbo_CDRMAIN.StartTime)-(dbo_cdrmain.GMTOffset) AS Adj_Unix_Time,
Format(([Adj_Unix_Time]/86400),"hh:nn:ss") AS Real_Time,

FROM
dbo_CDRMAIN

WHERE
(((dbo_CDRMAIN.LocalDay) Between 20080710 And 20080716) AND ((dbo_CDRMAIN.
TargetWGNum) In (5000,5001,5002,5600,5601,5602)))

ORDER BY dbo_CDRMAIN.LocalDay, dbo_CDRMAIN.StartTime;

Results:
LocalDay Adj_Unix_Time Real_Time
20080716 1216217982 14:19:42
20080716 1216218273 14:24:33
20080716 1216218375 14:26:15
20080716 1216218591 14:29:51
20080716 1216218596 14:29:56
20080716 1216218605 14:30:05
20080716 1216218623 14:30:23
20080716 1216218686 14:31:26
20080716 1216218693 14:31:33
20080716 1216218718 14:31:58
20080716 1216218725 14:32:05
20080716 1216218754 14:32:34
20080716 1216218766 14:32:46


Thanks, Wifor

 

[wifor via AccessMonster.com]

Thank you, I am going to try this Partition function. I have never used it so
I will play with it and see if I can get it to work.

Wifor

Better make the stop 60 as some could have 59 minutes and odd seconds.

Try using Partition function. Partition(number, start, stop, interval)
The 'number' is your date. Start = 0 Stop = 59

[quoted text clipped - 48 lines]

 

[wifor via AccessMonster.com]

Karl,

If I may ask for some further clarification on this as I am breaking down the
code and trying to understand it. In the Select statement you use the
'Format([Date open], "hh". I don't understand what Date open means? Do I
substitute a date field in there? not clear on this. Thanks, Wifor

Try using Partition function. Partition(number, start, stop, interval)
The 'number' is your date. Start = 0 Stop = 59
The inverval would be 15

SELECT Format([Date open],"hh") AS [Hour of day], Partition(Format([Date
open],"n"),0,59,15) AS [Count-per-interval], Count([Change Requests].[Date
open]) AS [CountOfDate open]
FROM [Change Requests]
WHERE ((([Change Requests].[Date open]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([Date open],"hh"), Partition(Format([Date open],"n"),0,59,15);

Greetings all,

[quoted text clipped - 37 lines]

Thanks, Wifor

 

[wifor via AccessMonster.com]

I am trying to modify this code and here is what I have so far. But I am
getting an OBDC-out of range error and it is prompting me for the 'Change
Requests' parameter. I know I am a moron at this so please be understanding.

Wifor

SELECT Format([dbo_cdrMain.starttime],"hh") AS [Hour of day],
Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15) AS [Count-per-interval]
,
Count([Change Requests].[Date open]) AS [CountOfDate open]
FROM [dbo_CDRMAIN]
WHERE ((([dbo_cdrMain].[localday]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([dbo_cdrMain.starttime],"hh"), Partition(Format([dbo_cdrMain.
starttime],"n"),0,59,15);

Try using Partition function. Partition(number, start, stop, interval)
The 'number' is your date. Start = 0 Stop = 59
The inverval would be 15

SELECT Format([Date open],"hh") AS [Hour of day], Partition(Format([Date
open],"n"),0,59,15) AS [Count-per-interval], Count([Change Requests].[Date
open]) AS [CountOfDate open]
FROM [Change Requests]
WHERE ((([Change Requests].[Date open]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([Date open],"hh"), Partition(Format([Date open],"n"),0,59,15);

Greetings all,

[quoted text clipped - 37 lines]

Thanks, Wifor

 

[KARL DEWEY]

[Change Requests].[Date open] was the table and field I was using.

Try this as it has your table and fields. I assume the both [localday] and
[stattime] are DateTime datatype fields.

SELECT dbo_CDRMAIN.localday, Format([dbo_cdrMain.starttime],"hh") AS [Hour
of day], Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15) AS
[Interval], Count(dbo_CDRMAIN.localday) AS [Count-per-interval]
FROM dbo_CDRMAIN
WHERE (((dbo_CDRMAIN.localday) Between #8/25/2008# And #8/26/2008#))
GROUP BY dbo_CDRMAIN.localday, Format([dbo_cdrMain.starttime],"hh"),
Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15);

--
KARL DEWEY
Build a little - Test a little

I am trying to modify this code and here is what I have so far. But I am
getting an OBDC-out of range error and it is prompting me for the 'Change
Requests' parameter. I know I am a moron at this so please be understanding.

Wifor

SELECT Format([dbo_cdrMain.starttime],"hh") AS [Hour of day],
Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15) AS [Count-per-interval]
,
Count([Change Requests].[Date open]) AS [CountOfDate open]
FROM [dbo_CDRMAIN]
WHERE ((([dbo_cdrMain].[localday]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([dbo_cdrMain.starttime],"hh"), Partition(Format([dbo_cdrMain.
starttime],"n"),0,59,15);

Try using Partition function. Partition(number, start, stop, interval)
The 'number' is your date. Start = 0 Stop = 59
The inverval would be 15

SELECT Format([Date open],"hh") AS [Hour of day], Partition(Format([Date
open],"n"),0,59,15) AS [Count-per-interval], Count([Change Requests].[Date
open]) AS [CountOfDate open]
FROM [Change Requests]
WHERE ((([Change Requests].[Date open]) Between #8/25/2008# And #8/26/2008#))
GROUP BY Format([Date open],"hh"), Partition(Format([Date open],"n"),0,59,15);

Greetings all,

[quoted text clipped - 37 lines]

Thanks, Wifor

 

[wifor via AccessMonster.com]

Well.....localday and starttime are number data types (localday: 20080818)
and (starttime: 1216217982). The starttime is in UNIX time. Converting it
to a 24 military time is a problem that I have not overcome in this query yet.


Wifor

[Change Requests].[Date open] was the table and field I was using.

Try this as it has your table and fields. I assume the both [localday] and
[stattime] are DateTime datatype fields.

SELECT dbo_CDRMAIN.localday, Format([dbo_cdrMain.starttime],"hh") AS [Hour
of day], Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15) AS
[Interval], Count(dbo_CDRMAIN.localday) AS [Count-per-interval]
FROM dbo_CDRMAIN
WHERE (((dbo_CDRMAIN.localday) Between #8/25/2008# And #8/26/2008#))
GROUP BY dbo_CDRMAIN.localday, Format([dbo_cdrMain.starttime],"hh"),
Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15);

I am trying to modify this code and here is what I have so far. But I am
getting an OBDC-out of range error and it is prompting me for the 'Change

[quoted text clipped - 27 lines]

 

[KARL DEWEY]

Try this ---
SELECT dbo_CDRMAIN.localday, Format(DateAdd("s",[dbo_cdrMain.starttime],
#1/1/1970#),"hh") AS [Hour of day],
Partition(Format(DateAdd("s",[dbo_cdrMain.starttime],
#1/1/1970#),"n"),0,60,15) AS [Interval], Count(dbo_CDRMAIN.localday) AS
[Count-per-interval]
FROM dbo_CDRMAIN
WHERE (((dbo_CDRMAIN.localday) Between 20080801 And 20080831))
GROUP BY dbo_CDRMAIN.localday, Format(DateAdd("s",[dbo_cdrMain.starttime],
#1/1/1970#),"hh"), Partition(Format(DateAdd("s",[dbo_cdrMain.starttime],
#1/1/1970#),"n"),0,60,15);

--
KARL DEWEY
Build a little - Test a little

Well.....localday and starttime are number data types (localday: 20080818)
and (starttime: 1216217982). The starttime is in UNIX time. Converting it
to a 24 military time is a problem that I have not overcome in this query yet.


Wifor

[Change Requests].[Date open] was the table and field I was using.

Try this as it has your table and fields. I assume the both [localday] and
[stattime] are DateTime datatype fields.

SELECT dbo_CDRMAIN.localday, Format([dbo_cdrMain.starttime],"hh") AS [Hour
of day], Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15) AS
[Interval], Count(dbo_CDRMAIN.localday) AS [Count-per-interval]
FROM dbo_CDRMAIN
WHERE (((dbo_CDRMAIN.localday) Between #8/25/2008# And #8/26/2008#))
GROUP BY dbo_CDRMAIN.localday, Format([dbo_cdrMain.starttime],"hh"),
Partition(Format([dbo_cdrMain.starttime],"n"),0,59,15);

I am trying to modify this code and here is what I have so far. But I am
getting an OBDC-out of range error and it is prompting me for the 'Change

[quoted text clipped - 27 lines]

Thanks, Wifor