奥门威尼斯网址SQL 时间戳转换为日期

by admin on 2019年9月8日
DATEADD(s, Timestamp + 8*3600, '1970-01-01 00:00:00')

时间戳转换

如何在不同编程语言中获取现在的Unix时间戳(Unix timestamp)?

Java time
JavaScript Math.round(new Date().getTime()/1000)
getTime()返回数值的单位是毫秒
Microsoft .NET / C# epoch = (DateTime.Now.ToUniversalTime().Ticks – 621355968000000000) / 10000000
MySQL SELECT unix_timestamp(now())
Perl time
PHP time()
PostgreSQL SELECT extract(epoch FROM now())
Python 先 import time 然后 time.time()
Ruby 获取Unix时间戳:Time.now 或 Time.new
显示Unix时间戳:Time.now.to_i
SQL Server SELECT DATEDIFF(s, ‘1970-01-01 00:00:00’, GETUTCDATE())
Unix / Linux date +%s
VBScript / ASP DateDiff("s", "01/01/1970 00:00:00", Now())
其他操作系统
(如果Perl被安装在系统中)
命令行状态:perl -e "print time"

如何在不同编程语言中实现Unix时间戳(Unix timestamp) → 普通时间?

Java String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date(Unix timestamp * 1000))
JavaScript 先 var unixTimestamp = new Date(Unix timestamp * 1000) 然后 commonTime = unixTimestamp.toLocaleString()
Linux date -d @Unix timestamp
MySQL from_unixtime(Unix timestamp)
Perl 先 my $time = Unix timestamp 然后 my ($sec, $min, $hour, $day, $month, $year) = (localtime($time))[0,1,2,3,4,5,6]
PHP date(‘r’, Unix timestamp)
PostgreSQL SELECT TIMESTAMP WITH TIME ZONE ‘epoch’ + Unix timestamp) * INTERVAL ‘1 second’;
Python 先 import time 然后 time.gmtime(Unix timestamp)
Ruby Time.at(Unix timestamp)
SQL Server DATEADD(s, Unix timestamp, ‘1970-01-01 00:00:00’)
VBScript / ASP DateAdd("s", Unix timestamp, "01/01/1970 00:00:00")
其他操作系统
(如果Perl被安装在系统中)
命令行状态:perl -e "print scalar(localtime(Unix timestamp))"

如何在不同编程语言中实现普通时间 → Unix时间戳(Unix timestamp)?

Java long epoch = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").parse("01/01/1970 01:00:00");
JavaScript var commonTime = new Date(Date.UTC(year, month – 1, day, hour, minute, second))
MySQL SELECT unix_timestamp(time)
时间格式: YYYY-MM-DD HH:MM:SS 或 YYMMDD 或 YYYYMMDD
Perl 先 use Time::Local 然后 my $time = timelocal($sec, $min, $hour, $day, $month, $year);
PHP mktime(hour, minute, second, day, month, year)
PostgreSQL SELECT extract(epoch FROM date(‘YYYY-MM-DD HH:MM:SS’));
Python 先 import time 然后 int(time.mktime(time.strptime(‘YYYY-MM-DD HH:MM:SS’, ‘%Y-%m-%d %H:%M:%S’)))
Ruby Time.local(year, month, day, hour, minute, second)
SQL Server SELECT DATEDIFF(s, ‘1970-01-01 00:00:00’, time)
Unix / Linux date +%s -d"Jan 1, 1970 00:00:01"
VBScript / ASP DateDiff("s", "01/01/1970 00:00:00", time)
(function($) {
    $.extend({
        myTime: {
            /**
             * 当前时间戳
             * @return <int>        unix时间戳(秒)  
             */
            CurTime: function(){
                return Date.parse(new Date())/1000;
            },
            /**              
             * 日期 转换为 Unix时间戳
             * @param <string> 2014-01-01 20:20:20  日期格式              
             * @return <int>        unix时间戳(秒)              
             */
            DateToUnix: function(string) {
                var f = string.split(' ', 2);
                var d = (f[0] ? f[0] : '').split('-', 3);
                var t = (f[1] ? f[1] : '').split(':', 3);
                return (new Date(
                        parseInt(d[0], 10) || null,
                        (parseInt(d[1], 10) || 1) - 1,
                        parseInt(d[2], 10) || null,
                        parseInt(t[0], 10) || null,
                        parseInt(t[1], 10) || null,
                        parseInt(t[2], 10) || null
                        )).getTime() / 1000;
            },
            /**              
             * 时间戳转换日期              
             * @param <int> unixTime    待时间戳(秒)              
             * @param <bool> isFull    返回完整时间(Y-m-d 或者 Y-m-d H:i:s)              
             * @param <int>  timeZone   时区              
             */
            UnixToDate: function(unixTime, isFull, timeZone) {
                if (typeof (timeZone) == 'number')
                {
                    unixTime = parseInt(unixTime) + parseInt(timeZone) * 60 * 60;
                }
                var time = new Date(unixTime * 1000);
                var ymdhis = "";
                ymdhis += time.getUTCFullYear() + "-";
                ymdhis += (time.getUTCMonth()+1) + "-";
                ymdhis += time.getUTCDate();
                if (isFull === true)
                {
                    ymdhis += " " + time.getUTCHours() + ":";
                    ymdhis += time.getUTCMinutes() + ":";
                    ymdhis += time.getUTCSeconds();
                }
                return ymdhis;
            }
        }
    });
})(jQuery);

背景

select now(); -- 2017-05-18 16:22:45

select UNIX_TIMESTAMP('2017-05-18')  -- 1495036800

select UNIX_TIMESTAMP('2017-05-18 16:23:21')  -- 1495095801

select UNIX_TIMESTAMP(now());-- 1495095868

select FROM_UNIXTIME(UNIX_TIMESTAMP(now())); -- 2017-05-18 16:24:56

select FROM_UNIXTIME(UNIX_TIMESTAMP(now()),'%Y-%m-%d') -- 2017-05-18

select FROM_UNIXTIME(UNIX_TIMESTAMP(now()),'%Y-%m-%d %H:%i:%s') -- 2017-05-18 16:28:22

其中Timestamp为10位的时间戳,+8*3600是获取中国北京时间(东八区)

使用方法:

数据库中经常出现需要UNIX时间戳与日期时间转换的场景,这里简单对其中的转换进行记录备忘

 

console.log($.myTime.DateToUnix('2014-5-15 20:20:20'));
console.log($.myTime.UnixToDate(1325347200));

UNIX时间戳转日期时间:FROM_UNIXTIME()

mysql> select FROM_UNIXTIME(1494254907);
+---------------------------+
| FROM_UNIXTIME(1494254907) |
+---------------------------+
| 2017-05-08 22:48:27       |
+---------------------------+
1 row in set (0.00 sec)

 

日期时间转UNIX时间戳:UNIX_TIMESTAMP()

mysql> select UNIX_TIMESTAMP('2017-05-08 22:48:27');     
+---------------------------------------+
| UNIX_TIMESTAMP('2017-05-08 22:48:27') |
+---------------------------------------+
|                            1494254907 |
+---------------------------------------+
1 row in set (0.00 sec)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图