11

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.

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 amounts.

The table is static, Won't be updating or deleting data.

0
13

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

12

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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