Use TIMESTAMPDIFF in table creation

Question

Use TIMESTAMPDIFF in table creation

Navigation

#1 by (0 votes)

1

I'm adding mysql support for my program, currently it 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 does not work because it seems to not accept TIMESTAMPDIFF, I do not want to have to create an update in my code for the Time field, I want to keep it in the same table in the sql server.

Importing shows me the following errors:

mysql sql sql-server

asked by anonymous 28.12.2017 / 16:03

1 answer

regex for url validation Socket.BeginSend Using lots of cpu

score 0 · Answer 1

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

See the following tutorials:

CREATE TABLE and Generated Columns

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;