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_MICROS

9min

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

Overview

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

Syntax
|
SELECT TIMESTAMP_MICROS(Int64)


Its input type is an int64 expression representing a UNIX timestamp in microseconds, and the return data type is a timestamp.

Examples

#Case 1: Basic TIMESTAMP_MICROS() function

The below example uses the TIMESTAMP_MICROS() function to convert a given UNIX timestamp in microseconds into a timestamp without a timezone:

Timestamp micros
|
SELECT TIMESTAMP_MICROS(2280419000000000) AS timestamp_microsvalues;


The final output will be as follows:

Output
|
+-----------------------------+
| timestamp_microsvalues      |
+-----------------------------+
| 2042-04-06 17:43:20         |
+-----------------------------+


#Case 2: TIMESTAMP_MICROS() function using columns

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

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

INSERT INTO timemirco_example VALUES 
('1350417000000000'),
('2130215000000000'),
('1110115000000000'),
('2310112000000000');

Display the table
|
SELECT * FROM timemirco_example;


The above query will show the following table:

Output
|
+--------------------+
| unix_timestamp     | 
+--------------------+
| 1350417000000000   |
| 2130215000000000   |
| 1110115000000000   |
| 2310112000000000   |
+--------------------+


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

Unix timestamp
|
SELECT unix_timestamp, TIMESTAMP_MICROS(unix_timestamp)
AS timestamp_value
FROM timemicro_example;


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

Output
|
+-------------------------+-----------------------+
| unix_timestamp          | timestamp_value       |
+-------------------------+-----------------------+
|1350417000000000         | 2012-10-16 19:50:00   |
|2130215000000000         | 2037-07-03 06:23:20   |
|1110115000000000         | 2005-03-06 13:16:40   |
|2310112000000000         | 2043-03-16 09:46:40   |
+-------------------------+-----------------------+




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