Unix时间戳
2010-05-15 14:55:31 
阿炯
概念：UNIX时间戳：Unix时间戳(英文为Unix epoch, Unix time, POSIX time 或 Unix timestamp)是从1970年1月1日0时0分0秒(UTC/GMT的午夜)开始所经过的秒数，不考虑闰秒。UNIX时间戳的0按照ISO 8601规范为：1970-01-01T00:00:00Z.一个小时表示为UNIX时间戳格式为：3600秒；一天表示为UNIX时间戳为86400秒，闰秒不计算（由于 UTC 包括了闰秒，但在 POSIX 时间中闰秒会被忽略以提供一种简便且兼容的计算时差的方法；因此 POSIX 时间转换后不一定是 UTC，尽管它也存在）。在大多数的UNIX系统中UNIX时间戳存储为32位，这样会引发2038年问题或Y2038。
时间 秒
1 分钟 60 秒
1 小时 3600 秒
1 天 86400 秒
1 周 604800 秒
1 月 (30.44 天) 2629743 秒
1 年 (365.24 天) 31556926 秒
为了实现垮平台在应用系统中记录时间的时候我们就可以使用记录UNIX时间戳的方法做到垮平台性。现在大多数的语言java、PHP、Perl等都支持直接取UNIX时间戳，将需要记录的时间记录为UNIX时间戳，这样就可以不同的数据库系统中的垮平 台性，对与时间的操作只要对时间戳操作就行了。下面简单介绍下相关处理的方法：
================================获取系统UNIX时间戳=========================
Perl time
PHP time()
Ruby Time.now (or Time.new). To display the epoch: Time.now.to_i
Python import time first, then time.time()
Java long epoch = System.currentTimeMillis()/1000;
Microsoft .NET C# epoch = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
VBScript/ASP DateDiff("s", "01/01/1970 00:00:00", Now())
MySQL SELECT unix_timestamp(now())
PostgreSQL SELECT extract(epoch FROM now());
SQL Server SELECT DATEDIFF(s, '19700101', GETDATE())
JavaScript Math.round(new Date().getTime()/1000.0) getTime() returns time in milliseconds.
Unix/Linux date +%s
Other OS's Command line: perl -e "print time" (If Perl is installed on your system)
================================将时间转换成UNIX时间戳=======================
Perl Use these Perl Epoch routines
PHP mktime(hour, minute, second, month, day, year) More information
Ruby Time.local(year, month, day, hour, minute, second, usec ) (or Time.gm for GMT/UTC input). To display add .to_i
Python import time first, then int(time.mktime(time.strptime('2000-01-01 12:34:00', '%Y-%m-%d %H:%M:%S')))
Java long epoch = new java.text.SimpleDateFormat ("dd/MM/yyyy HH:mm:ss").parse("01/01/1970 01:00:00");
VBScript/ASP DateDiff("s", "01/01/1970 00:00:00", time field) More information
MySQL SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD More information
PostgreSQL SELECT extract(epoch FROM date('2000-01-01 12:34'));
With timestamp: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
With interval: SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
SQL Server SELECT DATEDIFF(s, '19700101', time field)
JavaScript use the JavaScript Date object
Unix/Linux date +%s -d"Jan 1, 1980 00:00:01"
================================将UNIX时间戳转换成时间=======================
Perl Use these Perl Epoch routines
PHP date(output format, epoch); Output format example: 'r' = RFC 2822 date More information
Ruby Time.at(epoch)
Python import time first, then time.gmtime(epoch) time is an array of year, month, day, hour, min, sec, day of week, day of year, DST More information
Java String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000));
VBScript/ASP DateAdd("s", epoch, "01/01/1970 00:00:00") More information
PostgreSQL SELECT TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second';
MySQL from_unixtime(epoch, optional output format) The default output format is YYY-MM-DD HH:MM:SS More information
SQL Server DATEADD(s, epoch, '19700101')
JavaScript use the JavaScript Date object
Linux date -d @1190000000 (replace 1190000000 with your epoch, needs newer version of date)
Other OS's Command line: perl -e "print scalar(localtime(epoch))" (If Perl is installed) Replace 'localtime' with 'gmtime' for GMT/UTC time.
北京时间 2023-11-15T06:13:20，UNIX 时间将进入 1700000000 纪元。
2038年问题的动画演示
时间 秒
1 分钟 60 秒
1 小时 3600 秒
1 天 86400 秒
1 周 604800 秒
1 月 (30.44 天) 2629743 秒
1 年 (365.24 天) 31556926 秒
为了实现垮平台在应用系统中记录时间的时候我们就可以使用记录UNIX时间戳的方法做到垮平台性。现在大多数的语言java、PHP、Perl等都支持直接取UNIX时间戳，将需要记录的时间记录为UNIX时间戳，这样就可以不同的数据库系统中的垮平 台性，对与时间的操作只要对时间戳操作就行了。下面简单介绍下相关处理的方法：
================================获取系统UNIX时间戳=========================
Perl time
PHP time()
Ruby Time.now (or Time.new). To display the epoch: Time.now.to_i
Python import time first, then time.time()
Java long epoch = System.currentTimeMillis()/1000;
Microsoft .NET C# epoch = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
VBScript/ASP DateDiff("s", "01/01/1970 00:00:00", Now())
MySQL SELECT unix_timestamp(now())
PostgreSQL SELECT extract(epoch FROM now());
SQL Server SELECT DATEDIFF(s, '19700101', GETDATE())
JavaScript Math.round(new Date().getTime()/1000.0) getTime() returns time in milliseconds.
Unix/Linux date +%s
Other OS's Command line: perl -e "print time" (If Perl is installed on your system)
================================将时间转换成UNIX时间戳=======================
Perl Use these Perl Epoch routines
PHP mktime(hour, minute, second, month, day, year) More information
Ruby Time.local(year, month, day, hour, minute, second, usec ) (or Time.gm for GMT/UTC input). To display add .to_i
Python import time first, then int(time.mktime(time.strptime('2000-01-01 12:34:00', '%Y-%m-%d %H:%M:%S')))
Java long epoch = new java.text.SimpleDateFormat ("dd/MM/yyyy HH:mm:ss").parse("01/01/1970 01:00:00");
VBScript/ASP DateDiff("s", "01/01/1970 00:00:00", time field) More information
MySQL SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD More information
PostgreSQL SELECT extract(epoch FROM date('2000-01-01 12:34'));
With timestamp: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
With interval: SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
SQL Server SELECT DATEDIFF(s, '19700101', time field)
JavaScript use the JavaScript Date object
Unix/Linux date +%s -d"Jan 1, 1980 00:00:01"
================================将UNIX时间戳转换成时间=======================
Perl Use these Perl Epoch routines
PHP date(output format, epoch); Output format example: 'r' = RFC 2822 date More information
Ruby Time.at(epoch)
Python import time first, then time.gmtime(epoch) time is an array of year, month, day, hour, min, sec, day of week, day of year, DST More information
Java String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000));
VBScript/ASP DateAdd("s", epoch, "01/01/1970 00:00:00") More information
PostgreSQL SELECT TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second';
MySQL from_unixtime(epoch, optional output format) The default output format is YYY-MM-DD HH:MM:SS More information
SQL Server DATEADD(s, epoch, '19700101')
JavaScript use the JavaScript Date object
Linux date -d @1190000000 (replace 1190000000 with your epoch, needs newer version of date)
Other OS's Command line: perl -e "print scalar(localtime(epoch))" (If Perl is installed) Replace 'localtime' with 'gmtime' for GMT/UTC time.
北京时间 2023-11-15T06:13:20，UNIX 时间将进入 1700000000 纪元。
2038年问题的动画演示