Text only | Text with Images

Hosting & Domaining Forum

Hosting & Domaining development => Programming Discussion => Databases => Topic started by: worldtraveler on Oct 04, 2022, 01:50 AM

Title: DB Invalid value for 'created_at'
Post by: worldtraveler on Oct 04, 2022, 01:50 AM
What should I do when I see the (Invalid value for 'created_at') error message when importing a database? Below is the structure and data of the DB, including the categories, comments, menu, pages, and posts tables.

Code Select
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
 
--
-- Database: `skillcms`
--
 
-------------------------------------------------- --------
 
--
-- Structure of the `categories` table
--
 
CREATE TABLE `categories` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `created_at` DATE DEFAULT CURRENT_TIMESTAMP(),
  `updated_at` DATE DEFAULT CURRENT_TIMESTAMP()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump the `categories` table data
--
 
INSERT INTO `categories` (`id`, `name`, `created_at`, `updated_at`) VALUES
(2, 'Nature', '2020-03-30', '2020-03-30'),
(3, 'Sport', '2020-03-30', '2020-03-30'),
(5, 'News', '2020-03-30', '2020-03-30'),
(8, 'New', '2020-05-10', '2020-05-10');
 
-------------------------------------------------- --------
 
--
-- Structure of the `comments` table
--
 
CREATE TABLE `comments` (
  `id` INT(11) NOT NULL,
  `text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP(),
  `user_id` INT(11) DEFAULT NULL,
  `post_id` INT(11) DEFAULT NULL,
  `moderate` INT(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
--
-- Dump `comments` table data
--
 
INSERT INTO `comments` (`id`, `text`, `created_at`, `user_id`, `post_id`, `moderate`) VALUES
(1, 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.', '2020- 05-08 21:47:29', 1, 13, 1),
(2, 'The get method returns an Illuminate\\Support\\Collection object with results, where each result is an instance of the StdClass PHP class. You can get the value of each column by accessing the column as a property of the object: ', '2020- 05-08 22:46:27', 1, 13, 1),
(3, 'The get method returns an Illuminate\\Support\\Collection object with results, where each result is an instance of the StdClass PHP class. You can get the value of each column by accessing the column as a property of the object: ', '2020- 05-08 22:46:48', 10, 13, 1),
(4, 'Ideological "Part of text missing" participation. ', '2020-05-08 23:23:37', 2, 10, 1),
(14, 'fdsghbnfiltfgiuk,ig', '2020-05-10 11:47:30', 1, 17, 1);
 
-------------------------------------------------- --------
 
--
-- `menu` table structure
--
 
CREATE TABLE `menu` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `path` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-------------------------------------------------- --------
--
-- Structure of the `pages` table
--
 
CREATE TABLE `pages` (
  `id` INT(11) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `slug` VARCHAR(255) DEFAULT NULL,
  `content` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump `pages` table data
--
 
INSERT INTO `pages` (`id`, `title`, `slug`, `content`) VALUES
(1, 'Contacts', 'kontakty', '<ul>\r\n<li><b>Tel: +6659871646354</b></li>\r\n<li><b&gt ;Email: mail@email.foo</b></li>\r\n</ul>'),
(2, 'About Us', 'o-nas', '<p>By the way,"Part of text missing"{"level":3} -->\r\n<h3>Industry regulatory disclosure requirements requirements</h3>\r\n<!-- /wp:heading -->');
 
-------------------------------------------------- --------
 
--
-- Structure of the `posts` table
--
 
CREATE TABLE `posts` (
  `id` INT(11) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `slug` VARCHAR(100) DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `category_id` INT(11) NOT NULL,
  `image` VARCHAR(255) DEFAULT NULL,
  `user_id` INT(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump `posts` table data
--
 
INSERT INTO `posts` (`id`, `title`, `slug`, `content`, `created_at`, `category_id`, `image`, `user_id`, `updated_at`) VALUES
(6, 'First post', 'first_post', ' Comrades! "Part of text missing" needs. ', '2020-03-30 00:00:00', 2, '/public/uploaded/Lime.jpg', 1, '2020-05-10 12:01:51'),
(9, 'Second article', 'second', 'Publisher's part of text missing', '2020-05-10 12:40:25', 3, '/public/uploaded/IMG_1799.JPG', 1, ' 2020-05-10 12:40:25');
 
-------------------------------------------------- --------
 
--
-- `roles` table structure
--
 
CREATE TABLE `roles` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump `roles` table data
--
 
INSERT INTO `roles` (`id`, `name`) VALUES
(1, 'administrator'),
(2, 'moderator'),
(3, 'user');
 
-------------------------------------------------- --------
 
--
-- Structure of the `settings` table
--
 
CREATE TABLE `settings` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `slug` VARCHAR(255) DEFAULT NULL,
  `value` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump `settings` table data
--
 
INSERT INTO `settings` (`id`, `name`, `slug`, `value`) VALUES
(1, 'Number of displayed records per page', 'per_page_front', '4'),
(2, 'Number of records displayed in the admin panel', 'per_page_admin', '20');
 
-------------------------------------------------- --------
 
--
-- Structure of the `subscribes` table
--
 
CREATE TABLE `subscribes` (
  `id` INT(11) NOT NULL,
  `email` VARCHAR(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump `subscribes` table data
--
 
INSERT INTO `subscribes` (`id`, `email`) VALUES
(5, 'subscribe@test.com'),
(7, 'admin@mail.cms'),
(12, 'admin@skill.cms');
 
-------------------------------------------------- --------
--
-- Structure of the `users` table
--
 
CREATE TABLE `users` (
  `id` INT(11) NOT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `login` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  `role_id` INT(11) NOT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `about` text DEFAULT NULL,
  `fio` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dump `users` table data
--
 
INSERT INTO `users` (`id`, `email`, `login`, `password`, `role_id`, `avatar`, `about`, `fio`) VALUES
(1, 'admin@skill.cms', 'admin', '$2y$10$.FBFJOpXqB/Ee1yCNh.SdufTaABg1DjekGAbSMjxosISzksS1VFcC', 1, '/public/uploaded/avatar-11.jpg', 'But synthetic testing provides a wide range of (specialists) participation in the formation of an analysis of existing patterns of behavior.It is difficult to say why nanotechnologies, striving to supplant traditional production, can be exposed.', 'Adminov Admin Adminovich'),
(2, 'moderator@mail.com', 'editer', '$2y$10$vqDhvSfRzsL/5HzuHe27pudMGqVfek49H.PJcm7EU/KZsdWlvEzvq', 2, NULL, NULL, 'Moderator Moderat Moderastovich'),
(3, 'user@mail.com', 'guest', '$2y$10$vqDhvSfRzsL/5HzuHe27pudMGqVfek49H.PJcm7EU/KZsdWlvEzvq', 3, NULL, NULL, 'Ivanov Ivan Ivanovich'),
(10, 'martin@gmail.com', 'martin', '$2y$10$vqDhvSfRzsL/5HzuHe27pudMGqVfek49H.PJcm7EU/KZsdWlvEzvq', 3, NULL, NULL, 'Martin Luther');
 
--
-- Indexes of stored tables
--
 
--
-- Indexes of the `categories` table
--
ALTER TABLE `categories`
  ADD PRIMARY KEY(`id`);
 
--
-- Indexes of the `comments` table
--
ALTER TABLE `comments`
  ADD PRIMARY KEY(`id`),
  ADD KEY `comments_posts_id_fk` (`post_id`),
  ADD KEY `comments_users_id_fk`(`user_id`);
 
--
-- Indexes of the `menu` table
--
ALTER TABLE `menu`
  ADD PRIMARY KEY(`id`);
 
--
-- Indexes of the `pages` table
--
ALTER TABLE `pages`
  ADD PRIMARY KEY(`id`);
 
--
-- Indexes of the `posts` table
--
ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`,`category_id`),
  ADD KEY `fk_posts_category1` (`category_id`),
  ADD KEY `posts_users_id_fk`(`user_id`);
 
--
-- Indexes of the `roles` table
--
ALTER TABLE `roles`
  ADD PRIMARY KEY(`id`);
 
--
-- Indexes of the `settings` table
--
ALTER TABLE `settings`
  ADD PRIMARY KEY(`id`);
 
--
-- Indexes of the `subscribes` table
--
ALTER TABLE `subscribers`
  ADD PRIMARY KEY(`id`);
 
--
-- Indexes of the `users` table
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`,`role_id`),
  ADD KEY `fk_users_role`(`role_id`);
 
--
-- AUTO_INCREMENT for saved tables
--
 
--
-- AUTO_INCREMENT for the `categories` table
--
ALTER TABLE `categories`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
 
--
-- AUTO_INCREMENT for the `comments` table
--
ALTER TABLE `comments`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
 
--
-- AUTO_INCREMENT for the `menu` table
--
ALTER TABLE `menu`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT;
 
--
-- AUTO_INCREMENT for the `pages` table
--
ALTER TABLE `pages`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for the `posts` table
--
ALTER TABLE `posts`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;
 
--
-- AUTO_INCREMENT for the `roles` table
--
ALTER TABLE `roles`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
 
--
-- AUTO_INCREMENT for the `settings` table
--
ALTER TABLE `settings`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
 
--
-- AUTO_INCREMENT for the `subscribes` table
--
ALTER TABLE `subscribers`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
 
--
-- AUTO_INCREMENT for `users` table
--
ALTER TABLE `users`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
 
--
-- Foreign key constraints on saved tables
--
 
--
-- Foreign key constraints on the `comments` table
--
ALTER TABLE `comments`
  ADD CONSTRAINT `comments_posts_id_fk` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  ADD CONSTRAINT `comments_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
 
--
-- Foreign key constraints on the `posts` table
--
ALTER TABLE `posts`
  ADD CONSTRAINT `fk_posts_category1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `posts_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
 
--
-- Foreign key constraints on the `users` table
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Title: Re: Invalid value for 'created_at'
Post by: sam on Oct 04, 2022, 02:25 AM
To specify the date in a field of a certain type, you can use a trigger for initialization. An example of a trigger is shown below:

Code Select
CREATE TRIGGER set_date BEFORE INSERT ON categories
 FOR EACH ROW SET NEW.created_at = DATE(NOW())
Note that only DATETIME and TIMESTAMP type fields will automatically be set with the current date and time.
Title: Re: DB Invalid value for 'created_at'
Post by: XamiXryusha on Mar 11, 2025, 12:10 PM
In your case, the created_at column in the categories table is defined as a DATE type but defaults to CURRENT_TIMESTAMP(), which is a DATETIME function. This mismatch can cause issues during data import. To resolve this, you should change the created_at and updated_at fields in the categories table to DATETIME type instead of DATE.

Moreover, ensure that all date values in your INSERT statements conform to the YYYY-MM-DD HH:MM:SS format, particularly in the comments and posts tables. As a programmer, you should always validate your schema against your data to prevent such conflicts. If you can't get this right, maybe it's time to rethink your database design approach.
Text only | Text with Images