时间戳转换工具在线 - Unix Epoch | Unix时间戳开发者资源和代码片段

获取当前Unix时间戳

// Get current Unix timestamp (seconds)
const timestampInSeconds = Math.floor(Date.now() / 1000);
console.log(timestampInSeconds);

// Get current Unix timestamp (milliseconds)
const timestampInMilliseconds = Date.now();
console.log(timestampInMilliseconds);

将Unix时间戳转换为日期

// Convert Unix timestamp (seconds) to date
const timestamp = 1609459200; // 2021-01-01 00:00:00 UTC
const date = new Date(timestamp * 1000);
console.log(date.toISOString()); // "2021-01-01T00:00:00.000Z"

// Format date
console.log(date.toLocaleString()); // Localized format (depends on user settings)
console.log(
  date.getFullYear() + '-' + 
  String(date.getMonth() + 1).padStart(2, '0') + '-' + 
  String(date.getDate()).padStart(2, '0') + ' ' + 
  String(date.getHours()).padStart(2, '0') + ':' + 
  String(date.getMinutes()).padStart(2, '0') + ':' + 
  String(date.getSeconds()).padStart(2, '0')
); // "2021-01-01 00:00:00" (UTC)

将日期转换为Unix时间戳

// Convert date string to Unix timestamp
const dateString = '2021-01-01T00:00:00Z';
const timestamp = Math.floor(new Date(dateString).getTime() / 1000);
console.log(timestamp); // 1609459200

// Convert Date object to Unix timestamp
const date = new Date(2021, 0, 1); // Note: months start at 0 (0 represents January)
const timestampFromDate = Math.floor(date.getTime() / 1000);
console.log(timestampFromDate);

注意:

In JavaScript, Date.now() returns timestamp in milliseconds. If you need timestamp in seconds, divide by 1000.

JavaScript's Date object constructor accepts timestamp in milliseconds, so when converting from seconds, multiply by 1000.

获取当前Unix时间戳

import time
import datetime

# Get current Unix timestamp (seconds)
timestamp_seconds = int(time.time())
print(timestamp_seconds)

# Get current Unix timestamp (milliseconds)
timestamp_milliseconds = int(time.time() * 1000)
print(timestamp_milliseconds)

将Unix时间戳转换为日期

import time
import datetime

# Convert Unix timestamp to date using time module
timestamp = 1609459200  # 2021-01-01 00:00:00 UTC
date_from_timestamp = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(timestamp))
print(date_from_timestamp)  # '2021-01-01 00:00:00'

# Local timezone
local_date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(timestamp))
print(local_date)  # Displayed in local timezone

# Using datetime module
date_obj = datetime.datetime.fromtimestamp(timestamp)  # Local timezone
print(date_obj.strftime('%Y-%m-%d %H:%M:%S'))

# UTC timezone
date_obj_utc = datetime.datetime.utcfromtimestamp(timestdat)
print(date_obj_utc.strftime('%Y-%m-%d %H:%M:%S'))

将日期转换为Unix时间戳

import time
import datetime

# Convert date string to Unix timestamp
date_string = '2021-01-01 00:00:00'
date_format = '%Y-%m-%d %H:%M:%S'

# Using time module (assuming date string is in UTC)
timestamp = int(time.mktime(time.strptime(date_string, date_format)))
print(timestamp)  # Note: This will interpret based on local timezone

# Using datetime module
date_obj = datetime.datetime.strptime(date_string, date_format)
timestamp = int(date_obj.timestamp())
print(timestamp)  # Note: This will interpret based on local timezone

# Explicitly specify UTC timezone
from datetime import timezone
date_obj_utc = datetime.datetime.strptime(date_string, date_format).replace(tzinfo=timezone.utc)
timestamp_utc = int(date_obj_utc.timestamp())
print(timestamp_utc)

注意:

Python's time.time() returns a floating-point timestamp in seconds. For milliseconds, multiply by 1000.

For better timezone support when working with datetime module, consider installing the pytz library.

Note that Python will interpret times in local timezone by default, so pay attention to timezone issues when converting.

获取当前Unix时间戳

// Get current Unix timestamp (seconds)
long timestampInSeconds = System.currentTimeMillis() / 1000L;
System.out.println(timestampInSeconds);

// Get current Unix timestamp (milliseconds)
long timestampInMilliseconds = System.currentTimeMillis();
System.out.println(timestampInMilliseconds);

// Using Java 8+ Instant class
import java.time.Instant;

// Get current timestamp (seconds)
long epochSeconds = Instant.now().getEpochSecond();
System.out.println(epochSeconds);

// Get current timestamp (milliseconds)
long epochMillis = Instant.now().toEpochMilli();
System.out.println(epochMillis);

将Unix时间戳转换为日期

import java.util.Date;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

// Using traditional Date class (for all Java versions)
long timestamp = 1609459200; // 2021-01-01 00:00:00 UTC
Date date = new Date(timestamp * 1000L);

// Format date
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(sdf.format(date)); // Output formatted date string

// Using Java 8+ Time API
Instant instant = Instant.ofEpochSecond(timestamp);

// Convert to specific timezone (UTC)
DateTimeFormatter formatter = DateTimeFormatter
    .ofPattern("yyyy-MM-dd HH:mm:ss")
    .withZone(ZoneId.of("UTC"));
String formattedDate = formatter.format(instant);
System.out.println(formattedDate);

// Convert to local timezone
String localFormattedDate = formatter
    .withZone(ZoneId.systemDefault())
    .format(instant);
System.out.println(localFormattedDate);

将日期转换为Unix时间戳

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

// Using traditional Date class
String dateString = "2021-01-01 00:00:00";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

try {
    // Parse date string to Date object
    java.util.Date date = sdf.parse(dateString);
    
    // Get timestamp (seconds)
    long timestampInSeconds = date.getTime() / 1000L;
    System.out.println(timestampInSeconds);
    
    // Get timestamp (milliseconds)
    long timestampInMilliseconds = date.getTime();
    System.out.println(timestampInMilliseconds);
} catch (ParseException e) {
    e.printStackTrace();
}

// Using Java 8+ Time API
String dateStr = "2021-01-01 00:00:00";
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

// Assume input date is in UTC
LocalDateTime dateTime = LocalDateTime.parse(dateStr, formatter);
ZonedDateTime zonedDateTime = dateTime.atZone(ZoneId.of("UTC"));
long timestamp = zonedDateTime.toEpochSecond();
System.out.println(timestamp);

// If input date is in local timezone
ZonedDateTime localZonedDateTime = dateTime.atZone(ZoneId.systemDefault());
long localTimestamp = localZonedDateTime.toEpochSecond();
System.out.println(localTimestamp);

注意:

In Java, System.currentTimeMillis() returns timestamp in milliseconds. For seconds, divide by 1000.

For Java 8 and above, it's recommended to use the newer API in java.time package (such as Instant, LocalDateTime) for timestamp and datetime conversions.

Java's Date constructor accepts timestamp in milliseconds, so when converting from seconds, multiply by 1000.

获取当前Unix时间戳

// Get current Unix timestamp (seconds)
$timestampInSeconds = time();
echo $timestampInSeconds;

// Get current Unix timestamp (milliseconds)
$timestampInMilliseconds = round(microtime(true) * 1000);
echo $timestampInMilliseconds;

// Using DateTime class (PHP 5.2+)
$dateTime = new DateTime();
$timestampInSecondsDateTime = $dateTime->getTimestamp();
echo $timestampInSecondsDateTime;

将Unix时间戳转换为日期

// Convert Unix timestamp to date
$timestamp = 1609459200; // 2021-01-01 00:00:00 UTC

// Using date function
$dateFormatted = date('Y-m-d H:i:s', $timestamp);
echo $dateFormatted; // Output formatted date (local timezone)

// Specify UTC timezone
$dateFormattedUTC = gmdate('Y-m-d H:i:s', $timestamp);
echo $dateFormattedUTC; // Output formatted date (UTC)

// Using DateTime class (PHP 5.2+)
$dateTime = new DateTime();
$dateTime->setTimestamp($timestamp);
echo $dateTime->format('Y-m-d H:i:s'); // Output formatted date (local timezone)

// Specify timezone
$dateTimeUTC = new DateTime("@$timestamp"); // '@' prefix interprets as timestamp
$dateTimeUTC->setTimezone(new DateTimeZone('UTC'));
echo $dateTimeUTC->format('Y-m-d H:i:s'); // Output formatted date (UTC)

// Convert to Beijing time
$dateTimeBeijing = new DateTime("@$timestamp");
$dateTimeBeijing->setTimezone(new DateTimeZone('Asia/Shanghai'));
echo $dateTimeBeijing->format('Y-m-d H:i:s'); // Output formatted date (Beijing time)

将日期转换为Unix时间戳

// Convert date string to Unix timestamp
$dateString = '2021-01-01 00:00:00';

// Using strtotime function (assumes local timezone)
$timestamp = strtotime($dateString);
echo $timestamp;

// Specify timezone information
$dateStringWithTZ = '2021-01-01 00:00:00 UTC';
$timestampUTC = strtotime($dateStringWithTZ);
echo $timestampUTC;

// Using DateTime class (PHP 5.2+)
$dateTime = new DateTime($dateString); // Default uses local timezone
$timestamp = $dateTime->getTimestamp();
echo $timestamp;

// Specify timezone
$dateTimeUTC = new DateTime($dateString, new DateTimeZone('UTC'));
$timestampUTC = $dateTimeUTC->getTimestamp();
echo $timestampUTC;

// Convert date parts to timestamp
$year = 2021;
$month = 1;
$day = 1;
$hour = 0;
$minute = 0;
$second = 0;
$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
echo $timestamp;

注意:

In PHP, the time() function returns timestamp in seconds. For milliseconds, use microtime(true) and multiply by 1000.

When dealing with timezones, PHP uses the server's configured timezone by default. You can use date_default_timezone_set() to set the default timezone, or use the DateTimeZone class to specify a particular timezone.

The strtotime() function can parse most date string formats, but may fail with non-standard formats. In such cases, use DateTime::createFromFormat().

获取当前Unix时间戳

package main

import (
	"fmt"
	"time"
)

func main() {
	// Get current Unix timestamp (seconds)
	timestampInSeconds := time.Now().Unix()
	fmt.Println(timestampInSeconds)

	// Get current Unix timestamp (milliseconds)
	timestampInMilliseconds := time.Now().UnixMilli()
	fmt.Println(timestampInMilliseconds)

	// Get current Unix timestamp (nanoseconds)
	timestampInNanoseconds := time.Now().UnixNano()
	fmt.Println(timestampInNanoseconds)

	// Alternative method to calculate millisecond timestamp (for older Go versions)
	timestampInMillisAlt := time.Now().UnixNano() / int64(time.Millisecond)
	fmt.Println(timestampInMillisAlt)
}

将Unix时间戳转换为日期

package main

import (
	"fmt"
	"time"
)

func main() {
	// Convert Unix timestamp (seconds) to date
	timestamp := int64(1609459200) // 2021-01-01 00:00:00 UTC
	
	// Create time object
	t := time.Unix(timestamp, 0)
	
	// Format date (UTC)
	fmt.Println(t.UTC().Format("2006-01-02 15:04:05"))
	
	// Format date (local timezone)
	fmt.Println(t.Format("2006-01-02 15:04:05"))
	
	// Specify timezone
	loc, err := time.LoadLocation("Asia/Shanghai")
	if err != nil {
		fmt.Println(err)
		return
	}
	
	// Format date (Beijing time)
	fmt.Println(t.In(loc).Format("2006-01-02 15:04:05"))
	
	// Convert millisecond timestamp to date
	timestampMillis := int64(1609459200000) // Millisecond representation of 2021-01-01 00:00:00 UTC
	tMillis := time.Unix(timestampMillis/1000, (timestampMillis%1000)*int64(time.Millisecond))
	fmt.Println(tMillis.UTC().Format("2006-01-02 15:04:05.000"))
}

将日期转换为Unix时间戳

package main

import (
	"fmt"
	"time"
)

func main() {
	// Convert date string to Unix timestamp
	dateString := "2021-01-01 00:00:00"
	
	// Parse date string (assuming UTC time)
	t, err := time.Parse("2006-01-02 15:04:05", dateString)
	if err != nil {
		fmt.Println(err)
		return
	}
	
	// Get Unix timestamp (seconds)
	timestamp := t.Unix()
	fmt.Printf("Timestamp (seconds): %d\n", timestamp)
	
	// Get Unix timestamp (milliseconds)
	timestampMillis := t.UnixMilli()
	fmt.Printf("Timestamp (milliseconds): %d\n", timestampMillis)
	
	// Parse date string with specified timezone
	loc, err := time.LoadLocation("Asia/Shanghai")
	if err != nil {
		fmt.Println(err)
		return
	}
	
	// Assume date string is in Beijing time
	tLoc, err := time.ParseInLocation("2006-01-02 15:04:05", dateString, loc)
	if err != nil {
		fmt.Println(err)
		return
	}
	
	// Get Unix timestamp (seconds)
	timestampLoc := tLoc.Unix()
	fmt.Printf("Timestamp for Beijing time (seconds): %d\n", timestampLoc)
	
	// Create time object for specific date and time
	specificTime := time.Date(2021, 1, 1, 0, 0, 0, 0, time.UTC)
	specificTimestamp := specificTime.Unix()
	fmt.Printf("Timestamp for specific date (seconds): %d\n", specificTimestamp)
}

注意:

Go's time formatting syntax is unique, using the reference time "2006-01-02 15:04:05" for layout, instead of format placeholders like Y-m-d H:i:s used in other languages.

In Go, time.Now().Unix() returns timestamp in seconds. For milliseconds, use time.Now().UnixMilli() (Go 1.17+) or time.Now().UnixNano() / int64(time.Millisecond).

When handling timezones, Go uses UTC by default. To use a specific timezone, load it with time.LoadLocation().

获取当前Unix时间戳

using System;

class Program
{
    static void Main()
    {
        // Get current Unix timestamp (seconds)
        DateTimeOffset now = DateTimeOffset.UtcNow;
        long timestampInSeconds = now.ToUnixTimeSeconds();
        Console.WriteLine(timestampInSeconds);
        
        // Get current Unix timestamp (milliseconds)
        long timestampInMilliseconds = now.ToUnixTimeMilliseconds();
        Console.WriteLine(timestampInMilliseconds);
        
        // Alternative method (compatible with older .NET versions)
        DateTime nowDt = DateTime.UtcNow;
        DateTime unixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        TimeSpan timeSpan = nowDt - unixEpoch;
        
        long timestampSecondsAlt = (long)timeSpan.TotalSeconds;
        Console.WriteLine(timestampSecondsAlt);
        
        long timestampMillisecondsAlt = (long)timeSpan.TotalMilliseconds;
        Console.WriteLine(timestampMillisecondsAlt);
    }
}

将Unix时间戳转换为日期

using System;

class Program
{
    static void Main()
    {
        // Convert Unix timestamp (seconds) to date
        long timestamp = 1609459200; // 2021-01-01 00:00:00 UTC
        
        // Using DateTimeOffset (.NET Standard 2.0+/.NET Framework 4.6+)
        DateTimeOffset dateTimeOffset = DateTimeOffset.FromUnixTimeSeconds(timestamp);
        Console.WriteLine(dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss")); // UTC time
        
        // Convert to local time
        DateTimeOffset localDateTimeOffset = dateTimeOffset.ToLocalTime();
        Console.WriteLine(localDateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss")); // Local time
        
        // Traditional method (works with all .NET versions)
        DateTime unixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        DateTime dateTime = unixEpoch.AddSeconds(timestamp);
        Console.WriteLine(dateTime.ToString("yyyy-MM-dd HH:mm:ss")); // UTC time
        
        // Convert to local time
        DateTime localDateTime = dateTime.ToLocalTime();
        Console.WriteLine(localDateTime.ToString("yyyy-MM-dd HH:mm:ss")); // Local time
        
        // Convert Unix timestamp (milliseconds) to date
        long timestampMs = 1609459200000; // 2021-01-01 00:00:00 UTC
        DateTimeOffset dateTimeOffsetMs = DateTimeOffset.FromUnixTimeMilliseconds(timestampMs);
        Console.WriteLine(dateTimeOffsetMs.ToString("yyyy-MM-dd HH:mm:ss.fff")); // Includes milliseconds
    }
}

将日期转换为Unix时间戳

using System;
using System.Globalization;

class Program
{
    static void Main()
    {
        // Convert date string to Unix timestamp
        string dateString = "2021-01-01 00:00:00";
        
        // Parse date string (assumed to be UTC time)
        DateTime dateTime = DateTime.ParseExact(
            dateString, 
            "yyyy-MM-dd HH:mm:ss", 
            CultureInfo.InvariantCulture, 
            DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal);
            
        // Using DateTimeOffset (.NET Standard 2.0+/.NET Framework 4.6+)
        DateTimeOffset dateTimeOffset = new DateTimeOffset(dateTime);
        long timestamp = dateTimeOffset.ToUnixTimeSeconds();
        Console.WriteLine($"Timestamp (seconds): {timestamp}");
        
        long timestampMs = dateTimeOffset.ToUnixTimeMilliseconds();
        Console.WriteLine($"Timestamp (milliseconds): {timestampMs}");
        
        // Traditional method (works with all .NET versions)
        DateTime unixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        TimeSpan timeSpan = dateTime - unixEpoch;
        
        long timestampAlt = (long)timeSpan.TotalSeconds;
        Console.WriteLine($"Timestamp (seconds): {timestampAlt}");
        
        // Convert Date object to Unix timestamp
        DateTime specificDate = new DateTime(2021, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        DateTimeOffset specificDateOffset = new DateTimeOffset(specificDate);
        long specificTimestamp = specificDateOffset.ToUnixTimeSeconds();
        Console.WriteLine($"Timestamp for specific date (seconds): {specificTimestamp}");
    }
}

注意:

In .NET Standard 2.0+ or .NET Framework 4.6+, prefer using DateTimeOffset class's ToUnixTimeSeconds(), ToUnixTimeMilliseconds() and FromUnixTimeSeconds(), FromUnixTimeMilliseconds() methods for handling Unix timestamps.

When handling datetime, be aware of timezone issues. In C#, DateTime has three different DateTimeKind values: Utc, Local, and Unspecified. When working with Unix timestamps, you should typically use Utc.

When parsing date strings, use the DateTime.ParseExact method to ensure parsing according to a specific format, avoiding issues due to regional settings.

获取当前Unix时间戳

# Get current Unix timestamp (seconds)
timestamp_seconds = Time.now.to_i
puts timestamp_seconds

# Get current Unix timestamp (milliseconds)
timestamp_milliseconds = (Time.now.to_f * 1000).to_i
puts timestamp_milliseconds

# Using Ruby's standard time library
require 'time'

# Get current UTC timestamp
utc_timestamp = Time.now.utc.to_i
puts utc_timestamp

将Unix时间戳转换为日期

require 'time'

# Convert Unix timestamp (seconds) to date
timestamp = 1609459200  # 2021-01-01 00:00:00 UTC

# Create Time object (defaults to local timezone)
time = Time.at(timestamp)
puts time.strftime('%Y-%m-%d %H:%M:%S')  # Formatted output in local timezone

# Create UTC Time object
time_utc = Time.at(timestamp).utc
puts time_utc.strftime('%Y-%m-%d %H:%M:%S')  # Formatted output in UTC timezone

# More formatting options
puts time_utc.strftime('%Y-%m-%d %H:%M:%S %z')  # Includes timezone offset
puts time_utc.iso8601  # ISO 8601 format

# Convert Unix timestamp (milliseconds) to date
timestamp_ms = 1609459200000  # 2021-01-01 00:00:00 UTC
time_ms = Time.at(timestamp_ms / 1000.0)  # Divide by 1000 to get seconds, keep decimal part
puts time_ms.strftime('%Y-%m-%d %H:%M:%S.%L')  # Formatted output including milliseconds

将日期转换为Unix时间戳

require 'time'

# Convert date string to Unix timestamp
date_string = '2021-01-01 00:00:00'

# Parse date string (assumed to be in local timezone)
time = Time.parse(date_string)
timestamp = time.to_i
puts "Local timezone timestamp (seconds): #{timestamp}"

# Parse date string (specified as UTC)
time_utc = Time.parse(date_string + ' UTC')
timestamp_utc = time_utc.to_i
puts "UTC timestamp (seconds): #{timestamp_utc}"

# Parse date string (millisecond timestamp)
timestamp_ms = (time_utc.to_f * 1000).to_i
puts "Millisecond timestamp: #{timestamp_ms}"

# More precise date parsing (using specific format)
require 'date'
datetime = DateTime.strptime(date_string, '%Y-%m-%d %H:%M:%S')
timestamp_from_datetime = datetime.to_time.to_i
puts "Timestamp from DateTime parsing (seconds): #{timestamp_from_datetime}"

# Using specific timezone
require 'tzinfo'
tz = TZInfo::Timezone.get('Asia/Shanghai')
time_in_zone = tz.local_to_utc(Time.parse(date_string))
timestamp_in_zone = time_in_zone.to_i
puts "Timestamp converted from Shanghai timezone (seconds): #{timestamp_in_zone}"

注意:

In Ruby, Time.now.to_i returns timestamp in seconds. For milliseconds, use (Time.now.to_f * 1000).to_i.

When handling timezones, Ruby's built-in Time class has some limitations. For complex timezone handling, consider using the tzinfo or activesupport gems for enhanced functionality.

Ruby's Time class constructor Time.at accepts timestamps in seconds by default, but can use floating-point numbers to represent timestamps with milliseconds.

When handling date strings, if no timezone is specified, Ruby typically assumes the local timezone, which can lead to unexpected conversion results.

获取当前Unix时间戳

-- MySQL
-- Get current Unix timestamp (seconds)
SELECT UNIX_TIMESTAMP() AS current_timestamp;

-- Get current Unix timestamp (milliseconds)
SELECT UNIX_TIMESTAMP(NOW(3)) * 1000 AS current_timestamp_ms;

-- PostgreSQL
-- Get current Unix timestamp (seconds)
SELECT EXTRACT(EPOCH FROM NOW()) AS current_timestamp;

-- Get current Unix timestamp (milliseconds)
SELECT EXTRACT(EPOCH FROM NOW()) * 1000 AS current_timestamp_ms;

-- SQLite
-- Get current Unix timestamp (seconds)
SELECT STRFTIME('%s', 'now') AS current_timestamp;

-- Get current Unix timestamp (milliseconds)
SELECT STRFTIME('%s', 'now') || SUBSTR(STRFTIME('%f', 'now'), 4) AS current_timestamp_ms;
-- Or simply multiply by 1000 (but not actual millisecond precision)
SELECT STRFTIME('%s', 'now') * 1000 AS current_timestamp_ms;

-- SQL Server
-- Get current Unix timestamp (seconds)
SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) AS current_timestamp;

-- Get current Unix timestamp (milliseconds)
SELECT DATEDIFF(MILLISECOND, '1970-01-01', GETUTCDATE()) AS current_timestamp_ms;

-- Oracle
-- Get current Unix timestamp (seconds)
SELECT (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 24 * 60 * 60 AS current_timestamp
FROM DUAL;

-- Get current Unix timestamp (milliseconds)
SELECT ((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 24 * 60 * 60 * 1000) +
       (TO_NUMBER(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'FF3'))) AS current_timestamp_ms
FROM DUAL;

将Unix时间戳转换为日期

-- MySQL
-- Convert Unix timestamp (seconds) to datetime
SET @timestamp = 1609459200; -- 2021-01-01 00:00:00 UTC
SELECT FROM_UNIXTIME(@timestamp) AS datetime;

-- Format date output
SELECT FROM_UNIXTIME(@timestamp, '%Y-%m-%d %H:%i:%s') AS formatted_datetime;

-- Convert to specific timezone
SELECT CONVERT_TZ(FROM_UNIXTIME(@timestamp), 'UTC', 'Asia/Shanghai') AS datetime_shanghai;

-- Convert millisecond timestamp to date (need to divide by 1000)
SET @timestamp_ms = 1609459200000;
SELECT FROM_UNIXTIME(@timestamp_ms / 1000) AS datetime_from_ms;

-- PostgreSQL
-- Convert Unix timestamp (seconds) to datetime
SELECT TO_TIMESTAMP(1609459200) AS datetime;

-- Format date output
SELECT TO_CHAR(TO_TIMESTAMP(1609459200), 'YYYY-MM-DD HH24:MI:SS') AS formatted_datetime;

-- Convert to specific timezone
SELECT TO_TIMESTAMP(1609459200) AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' AS datetime_shanghai;

-- Convert millisecond timestamp to date (need to divide by 1000)
SELECT TO_TIMESTAMP(1609459200000 / 1000.0) AS datetime_from_ms;

-- SQLite
-- Convert Unix timestamp (seconds) to datetime
SELECT DATETIME(1609459200, 'unixepoch') AS datetime;

-- Format date output
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 1609459200, 'unixepoch') AS formatted_datetime;

-- Convert millisecond timestamp to date (need to divide by 1000)
SELECT DATETIME(1609459200000 / 1000, 'unixepoch') AS datetime_from_ms;

-- SQL Server
-- Convert Unix timestamp (seconds) to datetime, for SQL Server 2016+
SELECT DATEADD(SECOND, 1609459200, '1970-01-01') AS datetime;

-- Convert datetime to Unix timestamp (milliseconds), for SQL Server 2016+
SELECT DATEDIFF(MILLISECOND, '1970-01-01', '2021-01-01 00:00:00') AS timestamp_ms;

-- Oracle
-- Convert datetime to Unix timestamp (seconds)
SELECT (TO_DATE('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - 
        TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 24 * 60 * 60 AS timestamp
FROM DUAL;

-- Convert datetime to Unix timestamp (milliseconds)
SELECT ((TO_DATE('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - 
         TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 24 * 60 * 60 * 1000) AS timestamp_ms
FROM DUAL;

将日期转换为Unix时间戳

-- MySQL
-- Convert datetime to Unix timestamp (seconds)
SET @date_string = '2021-01-01 00:00:00';
SELECT UNIX_TIMESTAMP(@date_string) AS timestamp;

-- Specify timezone
SELECT UNIX_TIMESTAMP(CONVERT_TZ(@date_string, 'Asia/Shanghai', 'UTC')) AS timestamp_from_shanghai;

-- PostgreSQL
-- Convert datetime to Unix timestamp (seconds)
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2021-01-01 00:00:00') AS timestamp;

-- Specify timezone
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2021-01-01 00:00:00+08') AS timestamp_with_tz;
-- Or
SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2021-01-01 00:00:00' AT TIME ZONE 'Asia/Shanghai')) AS timestamp_from_shanghai;

-- SQLite
-- Convert datetime to Unix timestamp (seconds)
SELECT STRFTIME('%s', '2021-01-01 00:00:00') AS timestamp;

-- SQL Server
-- Convert datetime to Unix timestamp (seconds), for SQL Server 2016+
SELECT DATEDIFF(SECOND, '1970-01-01', '2021-01-01 00:00:00') AS timestamp;

-- Convert datetime to Unix timestamp (milliseconds), for SQL Server 2016+
SELECT DATEDIFF(MILLISECOND, '1970-01-01', '2021-01-01 00:00:00') AS timestamp_ms;

-- Oracle
-- Convert datetime to Unix timestamp (seconds)
SELECT (TO_DATE('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - 
        TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 24 * 60 * 60 AS timestamp
FROM DUAL;

-- Convert datetime to Unix timestamp (milliseconds)
SELECT ((TO_DATE('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - 
         TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 24 * 60 * 60 * 1000) AS timestamp_ms
FROM DUAL;

注意:

Different database systems have significant variations in functions and syntax for handling Unix timestamps. The examples above cover the most commonly used database systems.

MySQL provides dedicated functions UNIX_TIMESTAMP() and FROM_UNIXTIME() for handling Unix timestamps.

PostgreSQL uses EXTRACT(EPOCH FROM ...) and TO_TIMESTAMP() functions to handle Unix timestamps.

Timezone handling is particularly important in databases, especially in cross-timezone applications. Make sure you understand how your database system handles timezone information.

Note that for SQL Server versions before 2016, different methods are needed to handle Unix timestamps, usually involving more complex calculations.

UnixEpoch

UnixEpoch

语言选择

Unix时间戳开发者资源和代码片段

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳

获取当前Unix时间戳

将Unix时间戳转换为日期

将日期转换为Unix时间戳