Adding Seconds - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding Seconds

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-25 : 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

3271 Posts

Posted - 2006-07-25 : 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

260 Posts

Posted - 2006-07-25 : 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

30421 Posts

Posted - 2006-07-25 : 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 is

oldValue                 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

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 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

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 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

30421 Posts

Posted - 2006-07-25 : 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

22859 Posts

Posted - 2006-07-25 : 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

30421 Posts

Posted - 2006-07-25 : 06:50:07

Most likely, yes.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 08:01:49

I wish I shared your confidence!

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-27 : 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

30421 Posts

Posted - 2006-07-27 : 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

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 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

30421 Posts

Posted - 2006-07-27 : 04:30:44

[code]-- 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[/code]

Peter Larsson
Helsingborg, Sweden

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-27 : 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

260 Posts

Posted - 2006-07-27 : 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

260 Posts

Posted - 2006-07-27 : 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

30421 Posts

Posted - 2006-07-27 : 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

391 Posts

Posted - 2006-07-27 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources