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 

TIMESTAMP_MILLIS

9min

This article will assist you in how to use the TIMESTAMP_MILLIS() function to convert a given UNIX timestamp in milliseconds to a timestamp.

Overview

The TIMESTAMP_MILLIS() function converts a given UNIX timestamp value in milliseconds since 1970-01-01 00:00:00 UTC into a timestamp. Its syntax can be seen below:

Syntax
|
SELECT TIMESTAMP_MILLIS(Int64)


Its input type is an int64 expression which represents a UNIX timestamp in milliseconds and the return data type is a timestamp.

Examples

#Case 1: Basic TIMESTAMP_MILLIS() function

The below example uses the TIMESTAMP_MILLIS() function to convert a given UNIX timestamp in milliseconds into a timestamp without a timezone.

Timestamp millis
|
SELECT TIMESTAMP_MILLIS(1671975000000) AS timestamp_millisvalues;


The final output will be as follows:

Output
|
+-----------------------------+
| timestamp_millisvalues      |
+-----------------------------+
| 2022-12-25 13:30:00         |
+-----------------------------+


#Case 2: TIMESTAMP_MILLIS() function using columns

Let’s suppose we have a table named unix_example with the following UNIX time values in milliseconds in the unix_timestamp column:

Create a table
|
CREATE TABLE unix_example (
  unix_timestamp long
);

INSERT INTO unix_timestamp VALUES 
('171472000000'),
('1671975000000'),
('153276000000');

Display the table
|
SELECT * FROM unix_example;


The above query will show the following table:

Output
|
+----------------+
| unix_timestamp | 
+----------------+
| 171472000000   |
| 1671975000000  |
| 153276000000   |
+----------------+


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

Unix timestamp
|
SELECT unix_timestamp, TIMESTAMP_MILLIS(unix_timestamp)
AS timestamp_value
FROM unix_example;


The output displays all the entries in the table in UNIX timestamp format (in milliseconds) in the unix_timestamp column and in the timestamp format in the column timestamp_value without timezone.

Output
|
+-------------------------+-----------------------+
| unix_timestamp          | timestamp_value       |
+-------------------------+-----------------------+
|171472000000             | 1975-06-08 15:06:40   |
|1671975000000            | 2022-12-25 13:30:00   |
|153276000000             | 1974-11-10 00:40:00   |
+-------------------------+-----------------------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
TIMESTAMP_SECONDS
NEXT
TIMESTAMP_MICROS
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Examples
#Case 1: Basic TIMESTAMP_MILLIS() function
#Case 2: TIMESTAMP_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