Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my SQL Server database, I have a datetime column. What is a good way to create a new column that represents the long value for the datetime column. The long would represent a number of seconds.

share|improve this question
    
@gbn typo fixed –  dublintech May 17 '12 at 8:55
    
May I ask: Why do You do it ? –  adopilot May 17 '12 at 9:11
1  
Number of seconds since when? Since you can calculate this in any query, why store it in an extra column? –  Aaron Bertrand May 17 '12 at 11:07
    
@adopilot I thought if I can convert it to longs it would make it easier to do group by queries over time periods as I could just divide the long number by fixed ammounts. –  dublintech May 17 '12 at 11:32
    
@Aaron the table is static, Won't be updating or deleting data. Stated actual problem in another post. But wanted to know this anyway. Sorry. –  dublintech May 17 '12 at 11:38
add comment

2 Answers

up vote 2 down vote accepted

Create a new column (ALTER TABLE) then run an UPDATE on it

UPDATE
  MyTable
SET
  NewIntColumn = DATEDIFF(SECOND, '19000101', MyDateTimeColumn)

19000101 is the SQL Server epoch. You can use 19700101 for Unix epoch for example

share|improve this answer
add comment

You can add a new column and manually update it as @gbn suggested, but now you have to constantly keep this column up to date with insert/update triggers or some other mechanism. Borrowing @gbn's guesses on table/column names, here are a couple of different approaches that don't require constant maintenance.

Computed Column

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, '19000101', MyDateTimeColumn));

--or for Unix epoch

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, '19700101', MyDateTimeColumn));

You could also persist and index this column, trading off query performance for storage, but you'll need to make a slight change to the calculation (trying to persist the above will yield an error about the computation being non-deterministic):

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, 0, MyDateTimeColumn)) PERSISTED;

-- or for Unix epoch

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, 25567, MyDateTimeColumn)) PERSISTED;

You would want to persist the column if you are more concerned about read performance than write performance (or storage).

View

One benefit of a view over a new column is that you don't have to change the base table schema (or worry about keeping it up to date). You pay the cost of computation at query time, which is the same as a non-persisted computed column.

CREATE VIEW dbo.vMyTable
AS
  SELECT -- other columns,
    MyDateTimeColumn,
    NewIntColumn = DATEDIFF(...whichever calc above makes sense...)
  FROM dbo.MyTable;

Runtime

Since the above calculations are not overly complex, just include the calculation in your query. Hopefully you are using stored procedures for data access so you aren't repeating this often.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.