Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
MySQL UNIX_TIMESTAMP() Function
2.1.
Syntax
2.2.
MYSQL
3.
Parameters of MySQL UNIX_TIMESTAMP() Function
3.1.
DATETIME or TIMESTAMP
3.2.
DATE
4.
Examples of MySQL UNIX_TIMESTAMP() Function
4.1.
MYSQL
4.2.
MYSQL
4.3.
MYSQL
4.4.
MYSQL
5.
Exceptions of UNIX_TIMESTAMP() Function
6.
Frequently Asked Questions
6.1.
What is the function of UNIX_TIMESTAMP?
6.2.
What is the function of a timestamp in MySQL?
6.3.
How to check timestamp in MySQL?
7.
Conclusion
Last Updated: Mar 27, 2024

MySQL UNIX TIMESTAMP Function

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

MySQL is a relational database management system (RDMS) based on SQL, which is highly popular for accessing and managing the relational database. MySQL is free and open-source software supported by Oracle Company. MySQL is scalable, fast, and easy to use compared to Oracle Database and Microsoft SQL Server.

MySQL UNIX_TIMESTAMP() FUNCTION

MySQL defined the database relationships in tabular form (collection or rows and columns). It consists of various queries which help in managing the database and manipulating data. In this article, we are only going to focus on MySQL UNIX_TIMESTAMP() Function.

MySQL UNIX_TIMESTAMP() Function

The MySQL UNIX_TIMESTAMP() is an inbuilt MySQL function that accepts only one argument: DATETIME or DATE data types and returns an unsigned integer. This unsigned integer is the number of seconds passed since 1970-01-01 00:00:00 UTC. 

If the Function UNIX_TIMESTAMP() is called with no argument, it will simply return the number of seconds passed from 1970-01-01 00:00:00 UTC until now. If we want to use different timezones, we can convert them using CONVERT_TZ() function.

Syntax

The syntax for the function UNIX_TIMESTAMP() in MySQL is as follows:

  • MYSQL

MYSQL

UNIX_PARAMETER(parameter);
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Parameters of MySQL UNIX_TIMESTAMP() Function

The parameters that we can pass to the MySQL UNIX_TIMESTAMP() Function are as follows:

  1. DATETIME or TIMESTAMP
  2. DATE

The parameter of the UNIX_TIMESTAMP() Function is optional. If no argument is passed to the Function, it will return the time passed since 1970-01-01 00:00:00 UTC.

Let’s look at both the parameters one by one in detail.

DATETIME or TIMESTAMP

The data types DATETIME or TIMESTAMP are used to store data with time instances. In MySQL, the format of data types is YYYY-MM-DD hh:mm:ss. In MySQL, The DATETIME or TIMESTAMP data types range between  “1000-01-01 00:00:00” to “9999-12-31 23:59:59”.

DATE

In MySQL, this data type is used to store a date only. The format of this data type is YYYY-MM-DD. In MySQL, the DATE datatype ranges between “1000-01-01” to “9999-12-31”.

Examples of MySQL UNIX_TIMESTAMP() Function

Case1: When no parameter is passed to the timestamp function

  • MYSQL

MYSQL

SELECT UNIX_TIMESTAMP() as Time;

Output:

output

Case 2: When the date value is given, and we have to find the number of seconds from the given date.

  • MYSQL

MYSQL

SELECT UNIX_TIMESTAMP('2000-04-02') as Time;

Output:

output

Case 3: When we have been provided with DateTime Value, we have to find the number of seconds from the provided DateTime instance.

  • MYSQL

MYSQL

SELECT UNIX_TIMESTAMP('2000-012-12 16:00:00') as Time;

Output:

output

Case 4: When seconds are given in the fractional form.

  • MYSQL

MYSQL

SELECT UNIX_TIMESTAMP('2021-01-13 20:32:00.2323') as Time;

Output:

output

Exceptions of UNIX_TIMESTAMP() Function

  • Other than the YYYY-MM-DD hh:mm:ss format, no other formats are supported in UNIX_TIMESTAMP() Function. If provided, an error will be thrown.
     
  • NULL is returned if the given input parameter is NULL.
     
  • If fractional seconds are given to UNIX_TIMESTAMP() Function, it calculates the time passed from the Unix epoch and adds the fractional part to the return value.
     
  • If we give input as a string, MySQL tries to convert it into a datetime or date value with the help of internal parsing rules. Though, the Function will return NULL.

Frequently Asked Questions

What is the function of UNIX_TIMESTAMP?

The UNIX_TIMESATMP() function in MySQL helps to return a UNIX timestamp. We can define a Unix timestamp as the no. of seconds elapsed since ‘1970-01-01 00:00:00’ UTC. 

What is the function of a timestamp in MySQL?

The TIMESTAMP() function in MySQL converts the datetime or date expression as a datetime value and returns the result in the form of a string.

How to check timestamp in MySQL?

To check the timestamp in MySQL, we can use the CURRENT_TIMESTAMP() function, which returns the current date and time.

Conclusion

In this article, we have discussed MySQL UNIX_TIMESTAMP() FunctionWe started with the introduction of MySQL, then we saw the syntax of MySQL UNIX_TIMESTAMP() Function, the we saw some examples.We hope this blog has helped you enhance your knowledge on MYSQL UNIX_TIMESTAMP() FUNCTION. If you want to learn more, then check out our articles:

Refer to our guided paths on Coding Ninjas Studio to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingJavaScriptSystem Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio! But suppose you have just started your learning process and are looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc.. In that case, you must have a look at the problemsinterview experiences, and interview bundles for placement preparations.

Nevertheless, consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!
 

Previous article
MySQL UNIX_TIMESTAMP() Function
Next article
Correlated Subquery
Live masterclass