时间戳

最新推荐文章于 2025-02-13 21:51:22 发布

elihe2011

最新推荐文章于 2025-02-13 21:51:22 发布

阅读量298

点赞数

CC 4.0 BY-SA版权

分类专栏： Shell 文章标签： timestamp

本文链接：https://blog.youkuaiyun.com/elihe2011/article/details/46388111

Shell 专栏收录该内容

22 篇文章

订阅专栏

本文介绍了如何使用不同的工具和技术（如 date、awk 和 Perl）在 Linux 系统中进行时间戳与日期时间之间的相互转换，并展示了 Oracle 数据库中的时间处理方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. date

# current time
[root@localhost ~]# date +%s
1433568941
[root@localhost ~]# date -d "@1433568941" "+%F %H:%M:%S"
2015-06-05 22:35:41

# convert date-time to timestamp
[root@localhost ~]# date -d "2010-10-01 20:00:01" +%s
1285988401
[root@localhost ~]# date -d "1970-01-01 utc 1285988401 seconds"
Fri Oct  1 20:00:01 PDT 2010
[root@localhost ~]# date -d "1970-01-01 14671 days"
Wed Mar  3 00:00:00 PST 2010

2. awk

# current time
[root@localhost ~]# awk 'BEGIN{print systime()}'
1433569433

# time format
[root@localhost ~]# echo "1433569433" | awk '{print strftime("%F %T", $0)}'
2015-06-05 22:43:53

# convert date-time to timestamp
[root@localhost ~]# awk 'BEGIN{print mktime("2015 06 05 22 43 53")}'
1433569433

3. perl

[root@localhost ~]# perl -e 'print time() . "\n"'
1433569988

[root@localhost ~]# perl -e 'print localtime(1433569988) . "\n"'
Fri Jun  5 22:53:08 2015

[root@localhost ~]# perl -MPOSIX -e 'print strftime("%Y-%m-%d %H:%M:%S\n",  localtime(1433569988))'
2015-06-05 22:53:08

4. orcale

--current time
SQL> select (sysdate - to_date('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400 from 
dual;                                                                         

(SYSDATE-TO_DATE('1970-1-18','YYYY-MM-DDHH24'))*86400                         
-----------------------------------------------------                         
                                           1433542457

-- session timezone
SQL> select tz_offset(sessiontimezone) from dual;                             

TZ_OFFS                                                                       
-------                                                                       
+00:00                                                                        

SQL> select sessiontimezone from dual;                                        

SESSIONTIMEZONE                                                               
------------------------------------------------
+00:00 

SQL> alter session set time_zone='+08:00';                                    

Session altered.                                                              

SQL> select tz_offset(sessiontimezone) from dual;                             

TZ_OFFS                                                                       
-------                                                                       
+08:00 

-- db timezone
SQL> select dbtimezone from dual;                                             

DBTIME                                                                        
------                                                                        
+00:00

-- nls_date_format
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';               

Session altered.                                                              

SQL> select current_date from dual;                                           

CURRENT_DATE                                                                  
-------------------                                                           
2015-06-06 14:29:06                                                           

SQL> select current_timestamp from dual;                                      

CURRENT_TIMESTAMP                                                             
---------------------------------------------------------------------------   
06-JUN-15 02.29.25.111827 PM +08:00                                           

SQL> select sysdate from dual;                                                

SYSDATE                                                                       
-------------------                                                           
2015-06-06 06:29:42 

----------------------------------------------
select to_date('19700101', 'yyyymmdd') + 1433542457/86400 + to_number(substr(TZ_OFFSET(sessiontimezone), 1, 3))/24 from dual;

select to_date('2015-6-4 20:07:31', 'yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01', 'yyyy-mm-dd')*86400 - to_number(substr(TZ_OFFSET(sessiontimezone), 1, 3)) * 3600 from dual;

select TZ_OFFSET(sessiontimezone) from dual;                    -- +08:00
select substr(TZ_OFFSET(sessiontimezone), 1, 3) from dual;          -- +08
select TO_NUMBER(substr(TZ_OFFSET(sessiontimezone), 1, 3)) from dual;   -- 8
-------------------------------------------------