Use TIMESTAMPDIFF in table creation

Asked

Viewed 133 times

1

I am adding mysql support to my program, currently only works with sql server, so I came across a problem with mysql.

Table sql server:

CREATE TABLE [dbo].[login_user](
    [idx] [int] IDENTITY(1,1) NOT NULL,
    [client_id] [int] NOT NULL,
    [Login] [datetime] NULL,
    [Logout] [datetime] NULL,
    [Time]  AS (datediff(second,[Login],[Logout])),

Mysql table:

CREATE TABLE IF NOT EXISTS `login_user` (
  `idx` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `login` DATETIME NULL,
  `Logout` DATETIME NULL,
  `Time` AS TIMESTAMPDIFF(second,`login`,`Logout`),
  PRIMARY KEY (`idx`)
) ENGINE=MyISAM;

Well the mysql table import doesn’t work because it seems n accept TIMESTAMPDIFF, I don’t want to have to create an update in my code for the field Time, want to keep in table equal is in sql server.

Importing shows me the following errors: inserir a descrição da imagem aqui

mysql sql sql-server

  • I believe you will need to use a Rigger to do this. But also try to remove the apostrophes from the fields in the function call, ie use TIMESTAMPDIFF(Login,Login,) instead of TIMESTAMPDIFF(Cond,login , Logout)

1 answer

0

You need to declare the type that will store the function result.

See the following tutorials:

The correct syntax is as follows:

CREATE TABLE IF NOT EXISTS `login_user` (
  `idx` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `login` DATETIME NULL,
  `Logout` DATETIME NULL,
  `time` INT AS (TIMESTAMPDIFF(second,`login`,`Logout`)),
  PRIMARY KEY (`idx`)
) ENGINE=MyISAM;

Browser other questions tagged mysql sql sql-server

You are not signed in. Login or sign up in order to post.