How to create a self-build DateTime field for 30 Minutes SQL Server?

Question

How to create a self-build DateTime field for 30 Minutes SQL Server?

Navigation

#1 by (2 votes)

0

I try to create a table that serves as a control for several tables whose structure is based on a day (24 hours) divided into 30 minute intervals.

In another table I have a list of activities, these last 30 minutes each. The point is that 2 activities can not hit the calendar.

To do this create this control table.

SQL Server

CREATE TABLE dbo.Control_Actividades
    (
    Id_Tiempo datetime NOT NULL PRIMARY KEY,
    Id_Actividad varchar(50) NULL
    )  ON [PRIMARY]

The point is that I want the Id_Tiempo field to be auto incremental in 30 minute intervals. I've searched several sites but I can not find any articles talking about this.

More details.

I apologize for not giving much detail of what I am doing, but it could be summarized in a record of future activities. I just wanted to clarify if it was possible to make a DateTime autoincrementar. I know it is not possible.

sql-server

asked by Jesse R. Jose 09.07.2017 в 15:37

source

1 answer

Error calling digest count of 2 tables in mysql

score 2 · Accepted Answer

I understand that there is no autoincremental at the date level, nor is it clear to me the need you have, because in the end you could have a INT IDENTIY(0,1800) that basically increases in ranges of 1800, which measured in seconds is just 30 minutes and then simply use the DATEADD adding the id in seconds to any date.

But if, yes or yes you want a date field with that displacement, with the method that I commented to you, you can do something closer to what you want:

CREATE TABLE #Ejemplo
(
    ID     INT IDENTITY (0,1800) NOT NULL, 
    DateID AS DATEADD(s, ID, '20170101'),
    Campo  VARCHAR(100)
)

This defines a DateId field that with each insert will generate a date 30 minutes higher from 01-01-2017 . But this has a problem, the field is "calculated", in reality it does not physically exist, so we can not use it in an index or primary key, for that we need it to be "persistent", something that is also complicated because DATEADD(s, ID, '20170101') is non-deterministic that is, the engine can not ensure that the value returned by the function will always be the same, to make it deterministic and persistent we have to create an additional date field in the table, something like this:

CREATE TABLE #Ejemplo
(
    ID        INT IDENTITY (0,1800) NOT NULL, 
    StartDate DATETIME DEFAULT(CONVERT(DATETIME, '20170101')),
    DateID    AS DATEADD(s, ID, StartDate) PERSISTED  PRIMARY KEY,
    Campo     VARCHAR(100)
)

Regardless of how we are going to create the table, we can do the following:

INSERT INTO #Ejemplo (Campo) VALUES ('Ejemplo 1');
INSERT INTO #Ejemplo (Campo) VALUES ('Ejemplo 2');
INSERT INTO #Ejemplo (Campo) VALUES ('Ejemplo 3');
INSERT INTO #Ejemplo (Campo) VALUES ('Ejemplo 4');

SELECT  *
    FROM #Ejemplo

DROP TABLE #Ejemplo

The output would be something like this:

ID          DateID                      Campo
=========== =========================== =========
1,00        01/01/2017 12:00:00 a.m.    Ejemplo 1
1.800,00    01/01/2017 12:30:00 a.m.    Ejemplo 2
3.600,00    01/01/2017 01:00:00 a.m.    Ejemplo 3
5.400,00    01/01/2017 01:30:00 a.m.    Ejemplo 4

I repeat again, that I find it hard to see the need to have the Date field like that, and that you investigate on the side of a% classic% since what I said is not optimal for us to say.