In this SQL tutorial we are going to discuss methods to convert Unix Timestamp to a date/time step by step for different databases so that you can understand the way completely once and for all. The timestamp in Unix is called Unix time, Epoch Time or POSIX and in RDBMSs we can use different methods to convert it into a date/time value.
It shows how much time has passed since 00:00:00 the Universal Coordinated Time since Thursday, January 1st, 1970. It’s the fundamental time standard by which the world adjusts time on their atomic clocks and is considered a successor to GMT. So we will make adjustments according to that as well.
Changing Unix Timestamp in SQL Server
Let’s start with the SQL Server. We need to start with 1970-01-01 as the standard date as mentioned above. So in SQL server we can use DATEDD() function here’s how it’s done technically.
SELECT DATEDD(s, 1860935119, '1970-01-01');we can use ‘s’, ‘ss’ or ‘second’ for the seconds. All will do that.
Changing Unix Timestamp in Oracle Database
So if we have to change the same date/time value in Oracle Database we need to do that with these steps. Remember like we have shown below:
- To construct the date of ‘1970-01-01’ we will use “TO_DATE()” method.
- To convert decimals into interval values we will use ‘NUMTODSINTERVAL()’
Both of these will give us ‘Date’ value. Please see below example to understand how actually it needs to be done:
SELECT
TO_DATE( '1970-01-01', 'YYYY-MM-DD' ) + NUMTODSINTERVAL( 1650321073, 'SECOND' )
FROM DUAL;Converting Unix Timestamp in MySQL and MariaDB
So basically changing date/time in MariaDB and MySQL is as simple as it can be. We will use the function “From_UNIXTIME()”. Once we enter it, it will show us time value in Unix timestamp.
SELECT FROM_UNIXTIME(1947172351);Converting Date/Time to Unix Timestamp in PostgreSQL
The next database we are going to change Unix Timestamp is Postgre. For this one we will use the function “TO_TIMESTAMP()” and that’s all for that. It will show us time. See the example below:
SELECT TO_TIMESTAMP(1912995045);Changing Unix Timestamp in SQLite
For SQLite we need to use its date and time function “DATETIME()” with “unixepoch” modifier. We have shown the example below with the function used for it.
SELECT DATETIME(1793956207, 'unixepoch');So this how we can edit timestamp in different databases with using their respective date/time functions. Let’s know in the comments for further queries. We will happily address them.
