website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

UNIX_MILLIS

9min

This article will assist you in using the UNIX_MILLIS() function to convert a given timestamp to a UNIX timestamp in milliseconds.

Overview

The UNIX_MILLIS() function returns a given timestamp to a UNIX timestamp in milliseconds from 1970-01-01 00:00:00-00 (can be negative). Its syntax is illustrated below:

Syntax
|
SELECT UNIX_MILLIS(TIMESTAMP)


Its input type is a TIMESTAMP expression, and the return data type is int64 representing time in milliseconds.

Examples

#Case 1: Basic UNIX_MILLIS() function

The below example uses the UNIX_MILLIS() function to convert a given timestamp into a UNIX timestamp in milliseconds:

Unix millis
|
SELECT UNIX_MILLIS(TIMESTAMP "1996-5-02 7:15:00+00") AS unix_millisvalues;


The final output will be as follows:

Output
|
+-----------------------------+
| unix_millisvalues           |
+-----------------------------+
| 831021300000.000000         |
+-----------------------------+


#Case 2: UNIX_MILLIS() function using columns

Let’s suppose we have a table named time_example with the following timestamp values in the time_stamp column:

Create a table
|
CREATE TABLE time_example (
  time_stamp timestamp
);

INSERT INTO time_example VALUES 
('2004-07-23 11:30:00+00'),
('2011-02-12 04:45:00+00'),
('1975-08-03 07:50:00+00');

Display the table
|
SELECT * FROM time_example;


The above query will show the following table:

Output
|
+-------------------------+
| time_example            | 
+-------------------------+
| 2004-07-23 11:30:00     |
| 2011-02-12 04:45:00     |
| 1975-08-03 07:50:00     |
+-------------------------+


We want to convert all timestamp values into UNIX timestamp values in milliseconds. To do that, we have to run the following query:

Unix timestamp
|
SELECT time_stamp, UNIX_MILLIS(time_stamp) AS time_millis FROM time_example;


The output displays all the timestamp entries of the table in the time_stamp column and the converted UNIX milliseconds timestamp entries in the column time_millis.

Output
|
+-------------------------+-----------------------+
| time_stamp              | time_millis           |
+-------------------------+-----------------------+
| 2004-07-23 11:30:00     | 1090582200000.000000  |
| 2011-02-12 04:45:00     | 1297485900000.000000  |
| 1975-08-03 07:50:00     | 176284200000.000000   |
+-------------------------+-----------------------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
UNIX_SECONDS
NEXT
UNIX_MICROS
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Examples
#Case 1: Basic UNIX_MILLIS() function
#Case 2: UNIX_MILLIS() function using columns

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla