unix_timestamp
This function is used to convert a date value to a numeric date value in UNIX format.
The function returns the first ten digits of the timestamp in normal UNIX format.
Syntax
unix_timestamp(string timestamp, string pattern)
Parameters
Parameter |
Mandatory |
Type |
Description |
---|---|---|---|
timestamp |
No |
DATE or STRING |
Date to be converted The following formats are supported:
|
pattern |
No |
STRING |
Format to be converted If this parameter is left blank, the default format yyyy-mm-dd hh:mm:ss is used. The value is a combination of the time unit (year, month, day, hour, minute, and second) and any character.
|
Return Values
The return value is of the BIGINT type.
![](https://support.huaweicloud.com/intl/en-us/sqlreference-dli/public_sys-resources/note_3.0-en-us.png)
- If the value of timestamp is NULL, NULL is returned.
- If both timestamp and pattern are left blank, the timestamp represented by the number of seconds since 1970-01-01 00:00:00 is returned.
Example Code
The value 1692149997 is returned.
select unix_timestamp('2023-08-16 09:39:57')
If the current system time is 2023-08-16 10:23:16, 1692152596 is returned.
select unix_timestamp();
The value 1692115200 (2023-08-16 00:00:00) is returned.
select unix_timestamp("2023-08-16 10:56:45", "yyyy-MM-dd");
select timestamp1, unix_timestamp(timestamp1) as date1_unix_timestamp, timestamp2, unix_timestamp(datetime1) as date2_unix_timestamp, timestamp3, unix_timestamp(timestamp1) as date3_unix_timestamp from database_t; output: +------------+-------------------------+-----------------------+---------------------- --+------------------------------------+----------------------------+ | timestamp1| date1_unix_timestamp | timestamp2 | date2_unix_timestamp | timestamp3 | date3_unix_timestamp | +------------+-------------------------+-----------------------+-------------------------+------------------------------------+----------------------------+ | 2023-08-02 | 1690905600000 | 2023-08-02 11:09:14 | 1690945754793 | 2023-01-11 00:00:00.123456789 | 1673366400000 | | 2023-08-03 | 1690992000000 | 2023-08-02 11:09:31 | 1690945771994 | 2023-02-11 00:00:00.123456789 | 1676044800000 | | 2023-08-04 | 1691078400000 | 2023-08-02 11:09:41 | 1690945781270 | 2023-03-11 00:00:00.123456789 | 1678464000000 | | 2023-08-05 | 1691164800000 | 2023-08-02 11:09:48 | 1690945788874 | 2023-04-11 00:00:00.123456789 | 1681142400000 | | 2023-08-06 | 1691251200000 | 2023-08-02 11:09:59 | 1690945799099 | 2023-05-11 00:00:00.123456789 | 1683734400000 | +------------+-------------------------+-----------------------+--------------------------+-----------------------------------+----------------------------+
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot