TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: MySQL TIMESTAMP with Examples
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
MySQL Tutorial

MySQL TIMESTAMP with Examples

Last updated: Oct 14, 2023 12:14 am
By Meenakshi Agarwal
Share
6 Min Read
MySQL TIMESTAMP with Simple Examples
MySQL TIMESTAMP with Simple Examples
SHARE

This tutorial explains MySQL TIMESTAMP and TIMESTAMP field characteristics such as automated initialization and updating. We’ll describe their usage with the help of simple examples.

Contents
SyntaxMySQL TIMESTAMP ExamplesTime zone exampleAuto Init and Update TIMESTAMP Field

Let’s now go through each of the sections one by one.

MySQL TIMESTAMP

The MySQL TIMESTAMP is a transient data type that contains a mixture of date and time. It is exactly 19 characters long. The structure of a TIMESTAMP field is as follows:

Syntax

# MySQL Timestamp
YYYY-MM-DD HH:MM:SS

The TIMESTAMP value shows in UTC by default. It starts from ‘1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’ UTC.

If you provide a TIMESTAMP value in the MySQL insert query, then it stores the value in UTC format. However, whenever you query the same field, MySQL converts it as per the connection’s time zone setting. Please be informed that this behavior does not apply to other temporal data types such as DATETIME. It is valid only for TIMESTAMP values.

Also, MySQL database time zone derives its default mode from the connection setting. However, you can modify the same through configuration changes.

It also implies that any TIMESTAMP value inserted via a client from a different timezone would display in connection time zone format. So, if you keep the timezone same, then it ensures to fetch the exact value you stored.

MySQL TIMESTAMP Examples

Let’s check out how to use TIMESTAMP values in MySQL queries.

Time zone example

To demonstrate, let’s first create a table named as Sample with sample_ts as a TIMESTAMP field.

CREATE TABLE Sample
    (
        sample_id int NOT NULL,
        sample_name VARCHAR(20),
        sample_ts TIMESTAMP
    );

Next, you have to set the timezone to ‘+00:00’ UTC by issuing the SET time_zone command.

SET TIME_ZONE = '+00:00';

After this, you have to insert a few rows while specifying TIMESTAMP values. See below.

INSERT INTO Sample
    (
        sample_id, sample_name, sample_ts
    )
VALUES
    (1, 'First Sample', '2016-01-01 00:00:01'),
    (2, 'Second Sample', '2017-01-01 00:00:01'),
    (3, 'Third Sample', '2018-01-01 00:00:01'),
    (4, 'Fourth Sample', '2019-01-01 00:00:01');

Now, view the table content by running the SELECT statement.

SELECT 
    *
FROM
    Sample;

Its output is as follows:

1	First Sample	2016-01-01 00:00:01
2	Second Sample	2017-01-01 00:00:01
3	Third Sample	2018-01-01 00:00:01
4	Fourth Sample	2019-01-01 00:00:01

Finally, change the session’s timezone, rerun the SELECT, and observe the value you receive from the MySQL database:

SET time_zone ='+04:00';

SELECT 
    *
FROM
    Sample;

This time, you’ll get a result showing a different timestamp value:

1	First Sample	2016-01-01 04:00:01
2	Second Sample	2017-01-01 04:00:01
3	Third Sample	2018-01-01 04:00:01
4	Fourth Sample	2019-01-01 04:00:01

Auto Init and Update TIMESTAMP Field

Let’s begin with how MySQL automatically initializes and updates the TIMESTAMP column with an example.

Here is the MySQL statement to create a table named Articles:

CREATE TABLE Artciles
    (
        articleId INT AUTO_INCREMENT PRIMARY KEY,
        articleTitle VARCHAR(60),
        dateCreated TIMESTAMP,
        datePublished TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

In the above table, there are two TIMESTAMP fields:

  • dateCreated – It represents the date when the author first wrote the article.
  • datePublished – The date-time when the author updated the article.

Since the creation date is static, so we need to supply an appropriate value for it. However, the other field would change as per the current time, so we used the CURRENT_TIMESTAMP as the default.

Let’s validate the auto initialization of the datePublished field. Below is the insert command to run.

INSERT INTO Artciles
    (
        articleTitle, dateCreated
    )
VALUES
    ('How to Use MySQL Insert Statement', '2019-07-21 00:00:01'),
    ('How to Use MySQL Select Statement', '2019-07-14 00:00:01'),
    ('How to Use MySQL Update Statement', '2019-07-07 00:00:01'),
    ('How to Use MySQL Delete Statement', '2019-07-01 00:00:01');

You can see that we only supplied the article title and creation date. MySQL will store default values for the rest of the fields.

SELECT 
    *
FROM
    Artciles;

The output of SELECT is:

1	How to Use MySQL Insert Statement	2019-07-21 00:00:01	2019-07-27 07:00:54
2	How to Use MySQL Select Statement	2019-07-14 00:00:01	2019-07-27 07:00:54
3	How to Use MySQL Update Statement	2019-07-07 00:00:01	2019-07-27 07:00:54
4	How to Use MySQL Delete Statement	2019-07-01 00:00:01	2019-07-27 07:00:54

Please note that you can use the auto init and update feature with DATETIME type as well in MySQL 5.6.5 and later versions. Besides, you can apply the below MACROs in more than one column.

  • DEFAULT_CURRENT_TIMESTAMP, and
  • UPDATE CURRENT TIMESTAMP

We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL TIMESTAMP. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.

You Might Also Like

MySQL vs MongoDB Comparison

Concatenate Strings in an SQL Query With Examples

The Difference between UPSERT & Insert

SQL Programming Test in 2024

SQL Table Creation: The Missing Manual

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article Generate random numbers list in Python Generate a List of Random Integers in Python
Next Article MySQL Data Types Explained with Examples MySQL Data Types Explained

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x