EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB Timezone
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB Timezone

By Sohel SayyadSohel Sayyad

MariaDB Timezone

Definition of MariaDB Timezone Function

MariaDB provides a time zone variable to maintain the track of several time zone settings. Basically, time_zone is a system variable and it is a primary way to set the time zone. We can use different formats to set the timezone of the MariaDB server, normally by default the value of the time zone is SYSTEM and it is used to indicate the system time zone by using the system_time_zone system variable. Some we can also use Coordinated Universal Time zone. If we loaded the time zone table in the mysql database then we can have permission to access different time zones such as Africa, America or Europe, etc.

Syntax:

set time_zone = ‘value’’

Explanation

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

In the above syntax, we use the set keyword with time_zone system variable to set the time zone of a specified country or area, which is dependent on user requirement.

How does Timezone function work in MariaDB?

Time zone uses a different parameter during the setting of the time_zone system variable, so let’s see how it works. Normally there are two different time zone settings we need to set within the MariaDB server such as global server time zone and time zone for the current session.

Global Server Time Zone

The global server time zone we can change by using two different ways such as by using settings in which we can set default time zone option and the alternate option is that by using the command line. We are also able to change the global server time zone by using dynamic settings but the user account has the permission of super privilege.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,754 ratings)

Session Time Zone

When a session is connecting to the server it also has its own local time zone we call session time zone. Basically, the session time zone is generated from the global value of the time_zone system variable. The session time zone we can change by using the dynamic setting of the time_zone system variable.

System Time Zone

The system time zone value we can determine when we start the server and it sets the value of system_time_zone system variable. Normally the system time zone fetches current time from the operating system. We can change the system time zone by using different ways as follows.

First way is that when we start the server with mysqlld_safe at that time we can set the system time zone with time zone value and another way is by using the command line option. If you are using the UNIX operating system then you can set the system time zone by setting the environment variable.

Time zone Effects on Functions

When we set a time zone variable at that time some functions are affected as follows.

NOW()

Now function is used to current date and time as a value in YYYY-MM-DD format. The return value is shown in the current time zone. The precision is an optional part of the now function and it is used to determine in microseconds.

Example:

Select now();

Explanation:

With the help of now function we can fetch the current date and time of the system. The final output of the above query we illustrate by using the following snapshot

MariaDB Timezone 1

Select now() +0;

Explanation

The final output of the above query we illustrate by using the following snapshot.

MariaDB Timezone 2

Let’s see some examples of now function with precision as follows.

select current_timestamp(2);

Explanation

In the above example, we use a precision value that is the current timestamp value as shown in the above statement. The final output of the above query we illustrate by using the following snapshot.

MariaDB Timezone 3

SYSDATE()

SYSDATE is also used to return data and time in YYYY-MM-DD format with time format. Precision part of the SYSDATE function is optional. Basic difference between NOW() and SYSDATE() is that SYSDATE() is used to return the time at which time is executed and NOW() function is used to return constant time at which time the statement began to execute.

Example:

select now(), sleep(4), now();

Explanation:

In the above example, we use now and sleep function to see the difference between now and sysdate function. The final output of the above query we illustrate by using the following snapshot.

MariaDB Timezone 4

SELECT SYSDATE(), SLEEP(4), SYSDATE();

Explanation

In the above example, we use the sysdate function with a sleep parameter as shown. The final output of the above query we illustrate by using the following snapshot.

MariaDB Timezone 5

CURDATE ()

It also returns the current date in format YYYY-MM-DD and it depends on whether the function is used a string or a number.

CURTIME ()

This function is used to returns current time in HHMMSS or HH:MM:SS format and it also depends on whether function is used a string or number.

UNIX_TIMESTAMP ()

If we call this function without an argument then it returns a UNIX timestamp as an unsigned integer. if we use uinix_timestamp() it returns a data argument with UTC value. Maximum value of Timestamp is 2147483647 equivalent to 2038-01-19 05:14:07.

SELECT UNIX_TIMESTAMP();

Explanation

In the above statement, we use UNIX_TIMESTAMP function to see the time stamp value without any argument. The final output of the above query we illustrate by using the following snapshot.

output

Some functions are not affected listed as follows.

  1. UTC_DATE()
  2. UTC_TIME()
  3. UTC_TIMESTAMP()

Examples

SHOW GLOBAL VARIABLES LIKE 'time_zone';

Explanation:

Suppose we need to see the global of the current server at that time we can use the above statement. In this statement we use the show global variable command with LIKE clause and time_zone is system variable. The final output of the above query we illustrate by using the following snapshot.

output 1

SET GLOBAL time_zone =”Africa/Johannesburg”;

Explanation

In the above example, we use a set global keyword with a system variable time_zone as shown in the above statement. Suppose the user needs to set a global time_zone as per requirement then the user can use the above statement. In this example ‘Africa/Johannesburg’ is we consider the value we can change that value. The final output of the above query we illustrate by using the following snapshot.

output 2

Conclusion

We hope from this article you have understood about the MariaDB time_zone. From this article we have learned the basic syntax of time_zone and as well as we also see different examples of time_zone. From this article, we learned how and when we use MariaDB time_zone.

Recommended Articles

This is a guide to MariaDB Timezone. Here we discuss the Definition, How Timezone function works in MariaDB? along with the examples. You may also have a look at the following articles to learn more –

  1. MariaDB AUTO_INCREMENT
  2. MariaDB Foreign Key
  3. MariaDB Commands
  4. MariaDB UPDATE
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more