| Author |
 Topic  |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
 Posted - 07/25/2006 : 02:37:38
|
Hi,
i have a huge table.One of the columns of the table is fTime, which is datetime data type. But there is NO seconds in the fTime column. What i wanna do is to add seconds to this column according to the example below;
2006-07-24 18:10:00----> 2006-07-24 18:10:00
2006-07-24 18:10:00----> 2006-07-24 18:10:59
2006-07-25 10:20:00----> 2006-07-25 10:20:00
2006-07-25 10:20:00----> 2006-07-25 10:20:15
2006-07-25 10:20:00----> 2006-07-25 10:20:30
2006-07-25 10:20:00----> 2006-07-25 10:20:45
2006-07-25 10:20:00----> 2006-07-25 10:20:59
2006-07-25 12:00:00----> 2006-07-25 12:00:00
2006-07-25 12:00:00----> 2006-07-25 12:00:30
2006-07-25 12:00:00----> 2006-07-25 12:00:59
Shortly, as you can understand from above, the first second should be 00, the last second should be 59 and the rest will be calculated by, 60/count of same fTime rows - except the last row. There is one important thing to consider, which is the speed of the statements which include those calculations because of the huge number of rows i my table.
thanks
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 07/25/2006 : 03:04:12
|
To be honest I'm a little bit confused about your objective but to add seconds to a datetime datatype all you do is this:
SELECT DATEADD(ss, 15, myDateField) AS newDate
DATEADD itself is really fast (it's a native sql server function) but you'll have to elaborate a bit on your problem to make it clear what you want to do.
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 07/25/2006 : 03:20:15
|
2006-07-24 18:10:00----> 2006-07-24 18:10:00 --------> I have 2 rows of the same fTime column so i just put 00 seconds to
2006-07-24 18:10:00----> 2006-07-24 18:10:59 the first row and 59 seconds to the last row thats easy.
2006-07-25 10:20:00----> 2006-07-25 10:20:00 -------> when i have more than one rows, again i put 00 second to the first
2006-07-25 10:20:00----> 2006-07-25 10:20:15 row and 59 seconds to the last one. To calculate the in between rows, i
2006-07-25 10:20:00----> 2006-07-25 10:20:30 need to make a little calculation. Skip the last row, there is 4 rows.
2006-07-25 10:20:00----> 2006-07-25 10:20:45 60 seconds / 4 rows = 15 seconds. We found the seconds of the
2006-07-25 10:20:00----> 2006-07-25 10:20:59 row number 2. Than for the third row add another 15 seconds and so on.
2006-07-25 12:00:00----> 2006-07-25 12:00:00 ------> Again the same story, first row is 00 seconds, last row is 59 seconds. 60
2006-07-25 12:00:00----> 2006-07-25 12:00:30 seconds / 2 rows (excluding the last row always) = 30 seconds. We put the
2006-07-25 12:00:00----> 2006-07-25 12:00:59 30 seconds to the row number 2.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/25/2006 : 04:07:37
|
Try this one! EDIT: No need for primary key.-- Prepare test data
declare @table table (dt datetime)
insert @table
select '2006-07-24 18:10:00' union all----> 2006-07-24 18:10:00
select '2006-07-24 18:10:00' union all----> 2006-07-24 18:10:59
select '2006-07-25 10:20:00' union all----> 2006-07-25 10:20:00
select '2006-07-25 10:20:00' union all----> 2006-07-25 10:20:15
select '2006-07-25 10:20:00' union all----> 2006-07-25 10:20:30
select '2006-07-25 10:20:00' union all----> 2006-07-25 10:20:45
select '2006-07-25 10:20:00' union all----> 2006-07-25 10:20:59
select '2006-07-25 12:00:00' union all----> 2006-07-25 12:00:00
select '2006-07-25 12:00:00' union all----> 2006-07-25 12:00:30
select '2006-07-25 12:00:00' union all----> 2006-07-25 12:00:59
select '2006-07-26 12:00:00' ----> 2006-07-26 12:00:00
-- Do the work
select w.dt oldValue,
dateadd(second, case when z.num * w.i = 60 then 59 else z.num * w.i end, w.dt) newValue
from (
select b0.i + b1.i + b2.i + b3.i + b4.i + b5.i Num
from (select 0 i union all select 1) b0
cross join (select 0 i union all select 2) b1
cross join (select 0 i union all select 4) b2
cross join (select 0 i union all select 8) b3
cross join (select 0 i union all select 16) b4
cross join (select 0 i union all select 32) b5
) z
inner join (
select dt,
count(*) c,
case when count(*) = 1 then 0 else 60.0 / (count(*) - 1) end i
from @table
group by dt
) w on w.c > z.num
order by w.dt,
z.num Output isoldValue newValue
----------------------- -----------------------
2006-07-24 18:10:00.000 2006-07-24 18:10:00.000
2006-07-24 18:10:00.000 2006-07-24 18:10:59.000
2006-07-25 10:20:00.000 2006-07-25 10:20:00.000
2006-07-25 10:20:00.000 2006-07-25 10:20:15.000
2006-07-25 10:20:00.000 2006-07-25 10:20:30.000
2006-07-25 10:20:00.000 2006-07-25 10:20:45.000
2006-07-25 10:20:00.000 2006-07-25 10:20:59.000
2006-07-25 12:00:00.000 2006-07-25 12:00:00.000
2006-07-25 12:00:00.000 2006-07-25 12:00:30.000
2006-07-25 12:00:00.000 2006-07-25 12:00:59.000
2006-07-26 12:00:00.000 2006-07-26 12:00:00.000
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 07/25/2006 06:17:56 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/25/2006 : 04:15:05
|
Something like this perhaps? [Revised to include data - thanks for the data Peso!]
DECLARE @MyTable TABLE
(
MyPK int IDENTITY NOT NULL,
MyCopyOriginal datetime NULL,
MyDateColumn datetime NOT NULL,
PRIMARY KEY
(
MyPK
)
)
insert @MyTable(MyDateColumn)
select '2006-07-24 18:10:00' union all
select '2006-07-24 18:10:00' union all
select '2006-07-25 10:20:00' union all
select '2006-07-25 10:20:00' union all
select '2006-07-25 10:20:00' union all
select '2006-07-25 12:00:00' union all
select '2006-07-25 12:00:00' union all
select '2006-07-25 12:00:00' union all
select '2006-07-25 10:20:00' union all
select '2006-07-25 10:20:00' union all
select '2006-07-26 12:00:00'
-- Copy MyDateColumn to MyCopyOriginal for later checking
UPDATE @MyTable
SET MyCopyOriginal = MyDateColumn
-- Make list of all the items which will be updated - this REQUIRES a Unique Primary Key on MyTable
DECLARE @WorkingData TABLE
(
W_ID int IDENTITY NOT NULL,
W_PK1 int NOT NULL,
-- W_PK2 SomeDataType NOT NULL, -- Include one column for each PK field
W_DateTime datetime NOT NULL,
PRIMARY KEY
(
W_ID
)
)
-- Create working data
INSERT INTO @WorkingData(W_PK1, /* W_PK2, ..., */ W_DateTime)
SELECT MyPK, /* PK2, ..., */ MyDateColumn
FROM @MyTable
ORDER BY MyDateColumn, MyPK /* , PK2, ..., */
-- Summary data - how many of each date/time exist
DECLARE @SummaryData TABLE
(
S_DateTime datetime NOT NULL,
S_MinID int NOT NULL, -- ID of lowest row with this date/time (will be left at 00 seconds)
S_MaxID int NOT NULL, -- ID of highest row with this date/time (will be set to 59 seconds)
S_Count int NOT NULL,
PRIMARY KEY
(
S_DateTime
)
)
-- Create summary data
INSERT INTO @SummaryData(S_DateTime, S_MinID, S_MaxID, S_Count)
SELECT W_DateTime, MIN(W_ID), MAX(W_ID), COUNT(*)
FROM @WorkingData
GROUP BY W_DateTime
HAVING COUNT(*) >= 2 -- Only the ones with 2 or more identical times need "calculated times"
-- Update original table
UPDATE U
SET MyDateColumn =
CASE WHEN W_ID = S_MinID THEN MyDateColumn
WHEN W_ID = S_MaxID THEN DATEADD(Second, 59, MyDateColumn)
ELSE DATEADD(Second, 60/(S_Count-1)*(W_ID - S_MinID), MyDateColumn)
END
FROM @WorkingData AS W
JOIN @SummaryData AS S
ON S_DateTime = W_DateTime
JOIN @MyTable AS U
ON U.MyPK = W_PK1
/* AND U.PK2 = W_PK2 */
SELECT *
FROM @MyTable
ORDER BY MyCopyOriginal, MyPK
You could manage without the @WorkingData temporary table IF you have a single-part unique index, or PK, on your MyTable - otherwise its too hard for my little brain!
The final update could be in batches, within a loop, if it is too slow (given that you said there are lots of records to update)
Kristen |
Edited by - Kristen on 07/25/2006 04:35:53 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/25/2006 : 06:25:33
|
"No need for primary key"
I wouldn't have thought to use a TallyTable Peso.
Any idea how efficient an UPDATE would be?
I was thinking the TempTable route to be able to use a PK:PK JOIN for the update, but I do need a full-size <g> TempTable to achieve that ... which may be rubbish for performance
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/25/2006 : 06:32:44
|
The reason for using tally table is that I can't figure out if poster wants a one-time update, or just doing this for presentation issues.
If for one-time update, staging tables are more efficient and quicker, as in your solution.
Peter Larsson
Helsingborg, Sweden |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/25/2006 : 06:40:00
|
"What i wanna do is to add seconds to this column according to the example below"
Clearly a one time UPDATE, eh? 
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/25/2006 : 06:50:07
|
Most likely, yes.
Peter Larsson
Helsingborg, Sweden |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/25/2006 : 08:01:49
|
| I wish I shared your confidence! |
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 07/27/2006 : 04:11:31
|
Hi,
i need an algorithm to do the sample below;
i have duplicate fTime datas.
fTime
2005-01-02 20:23:00.000
2005-01-02 20:23:00.000
2005-01-02 20:23:00.000
...
what i need to do is;
2005-01-02 20:23:01.01 ----> Add 1 second to the first fTime and also 1 Msecond.Then till 60, MSeconds will be increased one by one like below;
2005-01-02 20:23:01.02
2005-01-02 20:23:01.03
2005-01-02 20:23:01.04
2005-01-02 20:23:01.05
...
2005-01-02 20:23:01.58
2005-01-02 20:23:01.59 ----> when the Msecond comes to 60, the seconds will be increased by one then again Mseconds will be increased one by one, like below
2005-01-02 20:23:02.00
2005-01-02 20:23:02.01
2005-01-02 20:23:02.02
2005-01-02 20:23:02.03
...
2005-01-02 20:23:02.59
2005-01-02 20:23:03.00
2005-01-02 20:23:03.01
2005-01-02 20:23:03.02
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/27/2006 : 04:19:36
|
Are you sure you want this? Not evenly spread, but just adding?
What if there are no more than two records of
2005-01-02 20:23:00.000
2005-01-02 20:23:00.000
Should that be
2005-01-02 20:23:01.010
2005-01-02 20:23:01.020
or
2005-01-02 20:23:01.010
2005-01-02 20:23:59.590
like before?
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 07/27/2006 04:20:36 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/27/2006 : 04:20:26
|
I've already done the donkey work on this one, so I reckon you should ahve a go at the Fine Detail by yourself.
Happy to help if you get stuck though.
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/27/2006 : 04:30:44
|
-- Prepare test data
declare @table table (dt datetime)
insert @table
select '2006-07-24 18:10:00' union all
select '2006-07-24 18:10:00'
-- Do the work
select w.dt oldValue,
dateadd(ms, 1010 + 10 * z.num, w.dt) newValue
from (
select b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i Num
from (select 0 i union all select 1) b0
cross join (select 0 i union all select 2) b1
cross join (select 0 i union all select 4) b2
cross join (select 0 i union all select 8) b3
cross join (select 0 i union all select 16) b4
cross join (select 0 i union all select 32) b5
cross join (select 0 i union all select 64) b6
cross join (select 0 i union all select 128) b7
cross join (select 0 i union all select 256) b8
cross join (select 0 i union all select 512) b9
cross join (select 0 i union all select 1024) b10
cross join (select 0 i union all select 2048) b11
cross join (select 0 i union all select 4096) b12
where b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i < 5959
) z
inner join (
select dt,
count(*) c
from @table
group by dt
) w on w.c > z.num
order by w.dt,
z.num
Peter Larsson
Helsingborg, Sweden |
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 07/27/2006 : 04:49:23
|
Hi,
Peso Mseconds passes 60 like below;
2005-01-02 19:53:01.800 ---> what i need is 2005-01-02 19:53:01.590 then 2005-01-02 19:53:02.00 when it gets the 60th.
|
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 07/27/2006 : 04:56:48
|
I asked this question "i need an algorithm to do the sample below;
i have duplicate fTime datas..." because i have duplicates more than 60 even more than 100 rows. I m not dealing with no more than 2 rows, it was an other issue nothing to do with this one. And i m not adding Mseconds to the previous one because there was no duplicates more than 60.
|
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 07/27/2006 : 06:33:29
|
Peso,
the code you have sent to me, increments seconds when the MSeconds reaches to 1000? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/27/2006 : 09:48:02
|
Look at this-- Prepare test data
declare @table table (dt datetime)
insert @table
select '2006-07-24 18:10:00' union all
select '2006-07-24 18:10:00'
-- Do the work
select w.dt oldValue,
dateadd(second, 1 + (10 + 10 * z.Num) / 600, dateadd(ms, (10 + 10 * z.num) % 600, w.dt)) newValue
from (
select b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i Num
from (select 0 i union all select 1) b0
cross join (select 0 i union all select 2) b1
cross join (select 0 i union all select 4) b2
cross join (select 0 i union all select 8) b3
cross join (select 0 i union all select 16) b4
cross join (select 0 i union all select 32) b5
cross join (select 0 i union all select 64) b6
cross join (select 0 i union all select 128) b7
cross join (select 0 i union all select 256) b8
cross join (select 0 i union all select 512) b9
cross join (select 0 i union all select 1024) b10
cross join (select 0 i union all select 2048) b11
cross join (select 0 i union all select 4096) b12
where b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i < 5959
) z
inner join (
select dt,
count(*) c
from @table
group by dt
) w on w.c > z.num
order by w.dt,
z.num
Peter Larsson
Helsingborg, Sweden |
|
|
|
KenW
Constraint Violating Yak Guru
USA
391 Posts |
Posted - 07/27/2006 : 16:06:54
|
raysefo,
quote:
the code you have sent to me, increments seconds when the MSeconds reaches to 1000?
What would you expect it to do? 1000 milliseconds is 1 second, so seconds increments and MS is reset to zero.
Ken |
|
|
| |
Topic |
|
Adding Seconds
