TechBeamersTechBeamers
  • Python
    • Python Basic
    • Python OOP
    • Python Quiz
    • Python Examples
    • Python Interview
    • Python Selenium
    • Python Advanced
  • Java
    • Java Quiz
    • Java Interview
  • C
  • C#
  • SQL
  • MySQL
  • Selenium
    • Selenium Tutorial
    • TestNG Tutorials
    • Selenium Interview
    • Selenium Quiz
  • Testing
    • Software Testing
    • Manual Testing
    • Software Testing Quiz
    • Testing Interview Questions
  • Agile
  • More
    • Bookmarks
    • Customize
    • About Us
    • Contact
Search
More Tutorials
  • Agile Tutorial
  • Android Tutorial
  • Angular Tutorial
  • Linux Tutorial
  • TestNG Tutorial
Interview Q&A
  • Python Questions
  • Java Questions
  • PHP Questions
  • Web Dev Questions
  • Linux Questions
Coding Quizzes
  • Python Quiz
  • Java Quiz
  • Selenium Quiz
  • HTML CSS Quiz
  • Shell Script Quiz
ยฉ 2023 TechBeamers. All Rights Reserved.
Reading: MySQL FROM_UNIXTIME() Function
Share
Notification Show More
Aa
TechBeamersTechBeamers
Aa
  • Agile
  • Android
  • Angular
  • Best IDEs
  • C
  • C#
  • CPP
  • HTML
  • IOT
  • Java
  • Java Interview
  • Java Quiz
  • Linux
  • MySQL
  • PHP Interview
  • Python
  • Python Basic
  • Python Quiz
  • Python Examples
  • Python Advanced
  • Python Interview
  • Python OOP
  • Python Selenium
  • QA Interview
  • Selenium
  • Selenium Quiz
  • Selenium Interview
  • Shell Script Quiz
  • Software Testing
  • Software Testing Quiz
  • SQL Interview
  • Web Development
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Java Interview
    • C# Interview
    • PHP Interview
    • Python Interview
    • QA Interview
    • Selenium Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quiz
    • Java Quiz
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • ShellScript Quiz
    • C/C++ Quiz
  • Bookmarks
    • My Bookmarks
    • My Interests
Follow US
ยฉ 2023 TechBeamers. All Rights Reserved.
TechBeamers > Blog > MySQL Tutorial > MySQL FROM_UNIXTIME() Function
MySQL Tutorial

MySQL FROM_UNIXTIME() Function

Last updated: 2023/05/22 at 12:52 PM
By Meenakshi Agarwal
Share
4 Min Read
SHARE

This tutorial explains how to use the MySQL FROM_UNIXTIME() function with the help of examples. By using it, you can convert or display a UNIX_TIMESTAMP value to a timestamp.

Contents
MySQL FROM_UNIXTIME()SyntaxExamplesCalling FROM_UNIXTIME() in string contextPassing UNIX_TIMESTAMP() as input to FROM_UNIXTIME()Calling FROM_UNIXTIME() in numeric contextUsing FROM_UNIXTIME() format argumentPrint hour from MySQL FROM_UNIXTIME() outputSummary โ€“ MySQL FROM_UNIXTIME()

The output format is either YYYY-MM-DD-HH-MM-SS or YYYYMMDDHHMMSS, which depends on the calling context of the function. It means that FROM_UNIXTIME() would return a string for a string and a number in a numeric operation.

Please note that MySQL also has this function UNIX_TIMESTAMP(). It gives us a value in seconds since โ€˜1970-01-01 00:00:00โ€™ UTC as an unsigned number. We are going to use the result of this function in our examples.

MySQL FROM_UNIXTIME()

This function provides a date/datetime formatted in a Unix timestamp style. The return value represents the number of seconds elapsed since January 1, 1970, GMT, with 12:00:01 (EPOC TIME).

Syntax

It has the following template:

-- MySQL From_UNIXTIME functions
FROM_UNIXTIME(UNIX_TIMESTAMP, [FORMAT]);

The UNIX_TIMESTAMP parameter is a UNIX timestamp value. The FORMAT is an optional argument, and it describes the format of the output.

Letโ€™s now go through some of the examples using the MySQL FROM_UNIXTIME() function.

We think that the following topics would even help more along with this tutorial:

  • MySQL TIMESTAMP
  • MySQL Date and Date Functions

Examples

Calling FROM_UNIXTIME() in string context

In this example, we are passing plain EPOC time value in the string context.

-- Numeric context
SELECT FROM_UNIXTIME(99);
SELECT FROM_UNIXTIME(1441563925);
SELECT FROM_UNIXTIME(1565043000);

The above MySQL statements would produce the date in standard string format. Check below:

-- Output
1970-01-01 00:01:39
2015-09-06 18:25:25
2019-08-05 22:10:00

Passing UNIX_TIMESTAMP() as input to FROM_UNIXTIME()

Let us check out what happens when we use the return value of UNIX_TIMESTAMP() to pass as a parameter. Here, we like to demonstrate how one function handles the output of the other.

-- Calling one function as parameter of other
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP("2019-08-07"));
SELECT UNIX_TIMESTAMP("2019-08-07"), FROM_UNIXTIME(1565136000);
SELECT UNIX_TIMESTAMP(CURDATE()), FROM_UNIXTIME(1565198329);

The above MySQL statements would produce the date in standard string format. Check below:

-- Output
2019-08-07 00:00:00
1565136000 2019-08-07 00:00:00
1565136000 2019-08-07 17:18:49

Calling FROM_UNIXTIME() in numeric context

In this example, we are passing plain EPOC time value in the string as well as in numeric context.

-- Compare string vs. numeric output
SELECT FROM_UNIXTIME(1565136000);
SELECT FROM_UNIXTIME(1565136000) + 0;

The output is as follows:

-- Output
2019-08-07 00:00:00
20190807000000

Using FROM_UNIXTIME() format argument

Now, weโ€™ll fill up the second argument of FROM_UNIXTIME() function. And try to customize the output format.

-- Printing a formatted output using FROM_UNIXTIME()
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

The result of this command is as follows:

-- Output
2019 7th August 05:25:21 2019

Print hour from MySQL FROM_UNIXTIME() output

Weโ€™ll use DATE_FORMAT() along with FROM_UNIXTIME() to determine the format of the hour.

-- Print hour
SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%H');

The result after execution is:

-- Output
17

Summary โ€“ MySQL FROM_UNIXTIME()

We hope you should now feel comfortable in using the MySQL FROM_UNIXTIME() function. However, you can take up more examples and practice.

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

You Might Also Like

Create a New User in MySQL with Password

MySQL CURRENT_TIMESTAMP() Function

MySQL vs. PostgreSQL: Point by Point Comparison

Grant Privileges on a Database in MySQL

MySQL UPSERT | INSERT or UPDATE Whichever is Applicable

Sign Up For Daily Newsletter

Be keep up! Get the latest breaking news delivered straight to your inbox.
Loading
By signing up, you agree to our Terms of Use and acknowledge the data practices in our Privacy Policy. You may unsubscribe at any time.
Share This Article
Facebook Twitter LinkedIn Email Copy Link
Share
By Meenakshi Agarwal
Follow:
Meenakshi Agarwal is a founder of TechBeamers and has extensive experience in Software Programming and Testing. Here, she aims to share her expertise by providing excellent tutorials for beginners to learn Python, Java, Selenium, C, C++, CSharp, Angular, PHP, JavaScript, Agile, Manual and Automation Testing concepts.
Previous Article Python to Find Difference Between Two Lists Python to Find Difference Between Two Lists
Next Article How to Grant Privileges in MySQL Grant Privileges on a Database in MySQL
Leave a comment Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Tutorials

Learn how to do factorial in python
Factorial Program in Python with Examples
Python Examples June 9, 2023
4 ways to find all possible string permutation in Python
Find All Possible Permutation of a String in Python
Python Examples April 30, 2023
SQL Exercises with Sample Table and Demo Data
SQL Exercises โ€“ Complex Queries
SQL Interview May 10, 2020
//

From Python to Java and everything in between, weโ€™ve got you covered with practical and actionable advice designed to help you level up your coding skills.

Quick Link

  • MY BOOKMARKS
  • MY INTERESTSNew
  • CONTACT US
  • BLOG INDEX

Top Categories

  • AGILE
  • BEST IDEsHot
  • PYTHON
  • SQL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
ยฉ 2023 TechBeamers. All Rights Reserved.
  • Contact Page
  • About Us
  • Terms of Use
  • Privacy Policy
  • Disclaimer
Join Us!

Subscribe to our newsletter and never miss our latest news, podcasts etc..

Loading
Zero spam, Unsubscribe at any time.