精华内容
下载资源
问答
  • 了解SYSDATE函数

    2021-01-28 10:25:03
    SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function),该函数用...另外SYSDATE函数是用于获取数据库所在的操作系统的当前时间值的,所以它和数据库或会话的时区没有关系。我们可以使用NLS_DAT...

    SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function),该函数用以返回当前的日期与时间,常和DUAL伪表一起合作。SYSDATE实际上指的是SYSDATE()函数,但是因为这个函数没有参量,所以这里可以省略()圆括号。另外SYSDATE函数是用于获取数据库所在的操作系统的当前时间值的,所以它和数据库或会话的时区没有关系。

    我们可以使用NLS_DATE_FORMAT参数或者TO_CHAR函数来获得我们想要的SYSDATE日期格式,具体的格式代码如下:

    Format Code

    Explanation

    YEAR

    Year, spelled out

    YYYY

    4-digit year

    MM

    Month (01-12; JAN = 01).

    MON

    Abbreviated name of month.

    MONTH

    Name of month, padded with blanks to length of 9 characters.

    D

    Day of week (1-7).

    DAY

    Name of day.

    DD

    Day of month (1-31).

    DDD

    Day of year (1-366).

    DY

    Abbreviated name of day.

    HH

    Hour of day (1-12).

    HH12

    Hour of day (1-12).

    HH24

    Hour of day (0-23).

    MI

    Minute (0-59).

    SS

    Second (0-59).

    SSSSS

    Seconds past midnight (0-86399).

    具体的用法如:

    syntax:

    select to_char(sysdate,'FORMAT CODES') from dual;

    select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;

    TO_CHAR(SYSDATE,'YEAR-MON-DAY HH24:MI:SS')

    --------------------------------------------------------------------------------

    TWENTY ELEVEN-AUG-WEDNESDAY 21:34:43

    与SYSDATE不同,CURRENT_DATE()函数会返回数据库会话所设置的本地时区的当前日期。

    我们来具体看一下这2个函数所返回结果的差异:

    SQL> select * from v$version;

    BANNER

    --------------------------------------------------------------------------------

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    PL/SQL Release 11.2.0.2.0 - Production

    CORE 11.2.0.2.0 Production

    TNS for Linux: Version 11.2.0.2.0 - Production

    NLSRTL Version 11.2.0.2.0 - Production

    SQL> select * from global_name;

    GLOBAL_NAME

    --------------------------------------------------------------------------------

    www.oracledatabase12g.com

    SQL> alter session set nls_date_format='YYYY-MM-DD HH24:mi:ss';

    Session altered.

    SQL> set linesize 200 pagesize 1400;

    SQL> col sessiontimezone for a20

    SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;

    CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE

    ------------------- ------------------- ------ --------------------

    2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00

    /* 我们调整session的所在时区到+09:00 */

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

    Session altered.

    SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;

    CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE

    ------------------- ------------------- ------ --------------------

    2011-08-24 20:56:33 2011-08-24 19:56:33 +08:00 +09:00

    /* 可以看到current_date所返回的时间增长了一个小时,而SYSDATE不变 */

    另外请注意SYSDATE函数的类型并非是DATE类型,而是其特有的类型:

    SQL> create table mytime(t1 date);

    Table created.

    SQL> insert into mytime values (sysdate);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select dump(t1), dump(sysdate), dump( current_date) from mytime;

    DUMP(T1)

    --------------------------------------------------------------------------------

    DUMP(SYSDATE)

    --------------------------------------------------------------------------------

    DUMP(CURRENT_DATE)

    --------------------------------------------------------------------------------

    Typ=12 Len=7: 120,111,8,24,21,31,59

    Typ=13 Len=8: 219,7,8,24,20,32,12,0

    Typ=13 Len=8: 219,7,8,24,20,32,12,0

    以上可以看到sysdate和current_date都属于TYPE=13,而普通DATE类型为TYPE=12

    数据类型type=12代表DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.

    因为sysdate的时钟取自操作系统,所以DBA同样需要关心操作系统本身时区的设置。不当的时区设置可能导致数据库内部的定时作业系统紊乱,主要会影响使用DBMS_JOB调用的一些JOB,DBMS_JOB包不会考虑到时区的因素,而仅仅考虑sydate的值(DBMS_JOB uses the "date" datatype to store the start date/time and does not store/use timezone related information. JOBS who are scheduled are always executed when sysdate => next execution time. )。如果使用DBMS_SCHEDULER则会考虑具体的时区。

    此外一些使用夏令时的地区还会面临更复杂的DST问题,详细可以参考ORAganism的文章和MOS文档 &:

    DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]

    Applies to:

    Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.2 - Release: 9.0.1 to 11.2

    Information in this document applies to any platform.

    Purpose

    This note gives a overview of how DBMS_SCHEDULER / DBMS_JOB react on DST transitions and what

    to know when using timezone information with jobs.

    Scope and Application

    To be used when DBMS_SCHEDULER jobs run at a wrong time after a DST change or you have output

    from jobs selecting from TIMESTAMP WITH LOCAL TIMEZONE columns that is not correct.

    If things about timezones are not clear please have first a look at Note 340512.1 Timestamps

    & time zones - Frequently Asked Questions.

    DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained.

    When running trough DBMS_SCHEDULER or DBMS_JOB a job output using TIMESTAMP WITH LOCAL

    TIMEZONE gives the wrong time.This happens when you use DBMS_SCHEDULER or DBMS_JOB to run jobs

    who select information from TIMESTAMP WITH LOCAL TIMEZONE fields.

    The time in a TIMESTAMP WITH LOCAL TIMEZONE that will be returned depends on the SESSIONTIMEZONE.

    select sessiontimezone from dual;

    If a TIMESTAMP WITH LOCAL TIMEZONE field contains for example "20-DEC-2007 15:15 -08:00" and

    your session timezone is also -08:00 you will see 15:15.

    If you change the session timezone to (for example) -07:00 then you will see 16:15,

    witch is normal and intended.

    Jobs using DBMS_SCHEDULER and DBMS_JOB however use a SESSIONTIMEZONE set to UTC (= +00:00 ).

    Hence, above example will give 15 + 8 = 23:15 when selected trough a job.

    If you use jobs that provide TIMESTAMP WITH LOCAL TIMEZONE then you may need to

    included in the job an alter session to change the SESSIONTIMEZONE.

    execute immediate 'alter session set time_zone = ''-08:00''';

    If you want the job to follow DST rules then you need to use a named timezone that

    follows the DST rules you want to use:

    execute immediate 'alter session set time_zone = ''US/Pacific''';

    There is Enhancement Request 5479680 - INCORPORATE SESSIONTIMEZONE INFORMATION INTO

    JOB INFORMATION to a) use the session timezone of the submitting session as timezone and b)

    have a way to see with what timezone a job is submitted.

    Job runtime time after DST has changed.

    When using DBMS_JOB:

    DBMS_JOB uses the "date" datatype to store the start date/time and does not store/use timezone

    related information. JOBS who are scheduled are always executed when sysdate => next execution time.

    This means for DST transitions that:

    * Assuming here that during DST the time is going FORWARD from 1:59 am directly to 3:00 am (skip on hour)

    - any job scheduled between 2:00 am and 2:59 am will only be started at 3:00 am seen the period between

    2:00 am and 2:59 am does not exist. This means ALL jobs scheduled between 2:00 am and 2:59 am are started

    at 3:00 am at once.

    * Assuming here that during DST the time is going BACK from 2:59 am to 2:00 am (= add one hour) the

    jobs will be executed in the "first" 2:00 am - 2:59 am timeslot, if the interval is more then

    one hour then the next execution will simply the correct time - aldo the actual "absolute"

    interval will be one hour more then normal. If the "interval" is less then one hour then they will

    NOT be executed the "second time" time the 2:00 am- 2:59 am timeslot is there because the "next execution time"

    will be > 2:59 am. So if you have for example a job that runs every 5 minutes this will NOT be executed

    during the "second time" the 2:00 am - 2:59 am timeslot is there until "locale time" becomes 3:00 am (or later).

    Please also note that the next execution time is calculated AFTER the job has finished.

    A known issue: Note 1328496.1 Ora-01878: Specified Field Not Found In Datetime Or Interval in

    Alert.log after DST change When using DBMS_SCHEDULER.

    DBMS_SCHEDULER uses a TIMESTAMP WITH TIMEZONE field to store the starting time (and other time related

    information) of a job, unlike DBMS_JOB who uses a "date" column who has no concept of timezones.

    When checking the various DBMS_SCHEDULER time fields make sure you are using a NLS_TIMESTAMP_TZ_FORMAT

    that display's the timezone information

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    or for US customers:

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY/MM/DD HH24:MI:SS TZR TZD';

    By using the "TZR TZD" mask you can quickly see in what format the data is stored/defined:

    select start_date, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='';

    * If it returns a offset (ex: -08:00) then the job time is defined with a offset.

    Timezone offsets are by nature NOT "DST aware" seen they mean a fixed offset from UTC.

    So any job using a offset as timezone will run after a DST change at a wrong time.

    * If it returns a named timezone (ex: Japan ) then the time is defined with

    that timezone name but that timezone has no DST rules.

    Some named timezones are also not "DST aware" ( Japan for example) because that region simply does not use DST.

    * If it returns a named timezone and a DST prefix (ex: US/Pacific PST )

    then the time is defined with that timezone name and that timezone is DST aware.

    To have a job that automatically adjusts for DST you need to make sure it's defined

    with the proper named timezone name that actually uses DST rules.

    The DST information in Oracle is sometimes updated, when using a named timezone please check

    Note 412160.1 Updated Time Zones in Oracle Time Zone File patches.

    that note has a list of named timezones that changed and so you can see if you need to

    update the Oracle DST information.

    Please note that it's a good idea to update the Oracle DST definitions when using named

    timezones but that applying "a DST patch" will not always solve all scheduler problems.

    Most "scheduler dst" problems are due the usage of a offset, not a named timezone, when creating the jobs.

    Good to know:

    * In oracle the session timezone (select sessiontimezone from dual;) defaults to a offset ( like +05:00),

    even if the Operating system "TZ" variable is set to a named TZ,

    unless the ORA_SDTZ is set in the client (!) environment (or registry) with a *oracle* TZ name.

    * DBMS_SCHEDULER has no relation to or does not use the database timezone.

    When you submit a job using DBMS_SCHEDULER Oracle will use a different source to populate the timezone depending on:

    a) When start_date is NULL, DBMS_SCHEDULER will determine the time zone for the repeat interval as follows:

    1. It will check whether the session time zone is a region name. The session time

    zone can be set to a named timezone by either:

    * Issuing an ALTER SESSION statement, for example:

    SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';

    * Setting the ORA_SDTZ environment variable on the client (!) side.

    2. If the session time zone is an absolute offset instead of a region name, the Scheduler

    will use the value of the DEFAULT_TIMEZONE Scheduler attribute.

    SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');

    PL/SQL procedure successfully completed.

    To see the current defined DEFAULT_TIMEZONE Scheduler attribute you can issue:

    SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    Session altered.

    SQL> select dbms_scheduler.stime from dual;

    STIME

    ---------------------------------------------------------------------------

    06/03/2008 11:58:06 US/EASTERN EST

    3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of

    systimestamp when the job or window is enabled .The timezone returned by systimestamp is always a OFFSET.

    Conclusion: make sure your DEFAULT_TIMEZONE Scheduler attribute is set, and check the

    sessiontimezone if needed seen this may take precedence.

    Note: SELECT DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME FROM DUAL;

    is not related to the DEFAULT_TIMEZONE.

    DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME provides the TimeZone set on the OS level trough the

    TZ variable, it will return the named timezone only if the OS TZ setting is also known in Oracle.

    Only when no OS TZ variable is set it will report the DEFAULT_TIMEZONE.

    b) When start_date is not NULL then DBMS_SCHEDULER will use:

    1. the timezone specified in the start time

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    begin

    DBMS_SCHEDULER.DROP_JOB ('GIVETZ_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('GIVETZ_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>'19/11/2007 12:00:00 CET', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='GIVETZ_TEST';

    begin

    DBMS_SCHEDULER.DROP_JOB ('GIVETZ_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('GIVETZ_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>'19/11/2007 12:00:00 +01:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='GIVETZ_TEST';

    2. The SESSION timezone when not specified in the start time.

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    ALTER SESSION SET TIME_ZONE = '+01:00';

    begin

    DBMS_SCHEDULER.DROP_JOB ('NODTZ_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('NODTZ_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>'19/11/2007 12:00:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');

    end;

    /

    select start_date, repeat_interval, last_start_date,next_run_date from

    dba_scheduler_jobs where job_name='NODTZ_TEST';

    ALTER SESSION SET TIME_ZONE = 'CET';

    begin

    DBMS_SCHEDULER.DROP_JOB ('NODTZ_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('NODTZ_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>'19/11/2007 12:00:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');

    end;

    /

    select start_date, repeat_interval, last_start_date,next_run_date from

    dba_scheduler_jobs where job_name='NODTZ_TEST';

    3. The SESSION timezone when using CURRENT_TIMESTAMP

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    ALTER SESSION SET TIME_ZONE = '+01:00';

    begin

    DBMS_SCHEDULER.DROP_JOB ('CURRTS_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('CURRTS_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>current_timestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='CURRTS_TEST';

    ALTER SESSION SET TIME_ZONE = 'CET';

    begin

    DBMS_SCHEDULER.DROP_JOB ('CURRTS_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('CURRTS_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>current_timestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='CURRTS_TEST';

    4. The SYSTIMESTAMP offset when using SYSTIMESTAMP

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    ALTER SESSION SET TIME_ZONE = 'CET';

    begin

    DBMS_SCHEDULER.DROP_JOB ('SYSTIME_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('SYSTIME_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>systimestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='SYSTIME_TEST';

    The timezone returned by systimestamp is always a OFFSET.

    5. the SESSION timezone when using SYSDATE.

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

    ALTER SESSION SET TIME_ZONE = '+01:00';

    begin

    DBMS_SCHEDULER.DROP_JOB ('SYSDATE_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('SYSDATE_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>sysdate, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='SYSDATE_TEST';

    ALTER SESSION SET TIME_ZONE = 'CET';

    begin

    DBMS_SCHEDULER.DROP_JOB ('SYSDATE_TEST');

    end;

    /

    begin

    dbms_scheduler.create_job('SYSDATE_TEST','PLSQL_BLOCK','begin null; end;',

    start_date=>sysdate, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from

    dba_scheduler_jobs where job_name='SYSDATE_TEST';

    So the best option if to always specify a full start date with a timezone name that uses

    the DST rules you expect or to use for example CURRENT_TIMESTAMP and set the session timezone

    first to the timezone name that uses the DST rules you expect.

    You can also redefine existing jobs if you notice they are defined with a offset.

    A example using a explicit named timezone:

    begin

    dbms_scheduler.set_attribute('SYSDATE_TEST','start_date',to_timestamp_tz

    ('Europe/London 12:00 20-NOV-2007','TZR HH24:MI DD-MON-RRRR'));

    end;

    /

    select start_date, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs

    where job_name='SYSDATE_TEST';

    展开全文
  • MySQL中 NOW() 和 SYSDATE() 函数的作用相同,都是返回当前日期和时间值,格式为“YYYY-MM-DD HH:MM:SS”或“YYYYMMDDHHMMSS”,具体格式根据函数用在字符串或数字语境中而定。【实例 1】使用日期时间函数 NOW 和 ...

    MySQL中 NOW() 和 SYSDATE() 函数的作用相同,都是返回当前日期和时间值,格式为“YYYY-MM-DD HH:MM:SS”或“YYYYMMDDHHMMSS”,具体格式根据函数用在字符串或数字语境中而定。

    【实例 1】使用日期时间函数 NOW 和 SYSDATE 获取当前系统的日期和时间,输入的 SQL 语句和执行结果如下所示。

    mysql> SELECT NOW(),SYSDATE();

    +---------------------+---------------------+

    | NOW() | SYSDATE() |

    +---------------------+---------------------+

    | 2017-04-01 19:36:52 | 2017-04-01 19:36:52 |

    +---------------------+---------------------+

    1 row in set (0.04 sec)

    由运行结果可以看到,NOW 函数和 SYSDATE 函数返回的结果是相同的。

    虽然在 MySQL 中 NOW() 和 SYSDATE() 都表示当前时间,但是 NOW() 取的是语句开始执行的时间,而 SYSDATE() 取的是语句执行过程中动态的实时时间。

    【实例 2】先查询了 NOW() 和 SYSDATE(),然后 sleep 了 3 秒,再查询 NOW() 和 SYSDATE(),结果如下:

    mysql> select now(),sysdate(),sleep(3),now(),sysdate();

    +-----------------------+------------------------+-------------+-----------------------+---------------------+

    | now() | sysdate() | sleep(3) | now() | sysdate() |

    +-----------------------+------------------------+-------------+------------------- ---+---------------------+

    | 2019-02-27 10:59:39 | 2019-02-27 10:59:39 | 0 | 2019-02-27 10:59:39 | 2019-02-27 10:59:42 |

    +-----------------------+------------------------+-------------+-----------------------+---------------------+

    1 row in set (3.00 sec)

    由运行结果可以看出,NOW() 函数始终获取的是 SQL 语句开始执行的时间,而 SYSDATE() 函数则是动态获取的实时时间。

    展开全文
  • 在MySQL Performance Blog博客上看到一篇文章介绍now()和sysdate()函数。想起很多朋友专门问在MySQL里面提供now()和sysdate()函数,都是表示取得当前时间,他们之间有什么区别。我们下面来详细看一下首先大家可以看...

    在MySQL Performance Blog博客上看到一篇文章介绍now()和sysdate()函数。

    想起很多朋友专门问在MySQL里面提供now()和sysdate()函数,都是表示取得当前时间,他们之间有什么区别。我们下面来详细看一下

    首先大家可以看一下下面的一个诡异现象:

    1

    2

    3

    4

    5

    6

    7

    mysql>SELECT NOW(),SYSDATE();

    +---------------------+---------------------+

    |NOW()|SYSDATE()|

    +-----------------------------+----------------------------+

    |1999-01-0100:00:00|2012-12-0509:50:03|

    +---------------------------+----------------------------+

    1row inset(0.00sec)

    很有意思吧?

    sysdate()得到的时间是当前时间,而now()取出来的时间竟然是“1999-01-01 00:00:00”。

    首先申明,我不是PS或者修改得来的,你看完本文,我会教你在你的MySQL上也得出这样的结果。

    另外我们看一下,now()和sysdate()的另外一个区别:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    mysql>SELECT NOW(),SLEEP(2),NOW();

    +---------------------+----------+---------------------+

    |NOW()|SLEEP(2)|NOW(|

    +-----------------------------+--------------+-----------------------------+

    |2006-04-1213:47:36|0|2006-04-1213:47:36|

    +---------------------------+--------------+----------------------------+

    mysql>SELECT SYSDATE(),SLEEP(2),SYSDATE();

    +---------------------+----------+---------------------+

    |SYSDATE()|SLEEP(2)|SYSDATE()|

    +-----------------------------+--------------+----------------------------+

    |2006-04-1213:47:44|0|2006-04-1213:47:46|

    +---------------------------+--------------+----------------------------+

    在使用now()的情况下,虽然我们sleep了2秒,但是大家可以看到两次now()函数输出的结果都是’2006-04-1213:47:36′

    而使用sysdate()的情况下,是两个时间’2006-04-12 13:47:44′,’2006-04-12 13:47:46’,正好相差两秒。

    这个最终的原因,大家可以直接查看MySQL的reference对now()函数的解释:http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now

    我简单给大家翻译一下。

    now()函数,返回的是当前的时间。但是当前的时间是怎么取的列?

    首先,对于now()函数来说,它取的时间是语句开始执行的那个时间,并且在语句执行过程中,这个值都不会变。甚至于,你在执行一个存储过程或者触发器时,这个值都是一直不变的。

    这也就解释了,为什么sleep了2秒以后,在SELECT NOW(), SLEEP(2), NOW();语句中,取出的时间值是同一个:’2006-04-12 13:47:36’。

    然后:now()函数取的当前时间从哪里来?它取自mysql的一个变量”TIMESTAMP”。

    很奇怪吧?

    其实这个是由于MySQL的replication导致的。你可以想象一下,一个insert into  gguard values (3,now());语句在两台MySQL上插入的值是不是一样?now()如果像sysdate()一样取的是机器的系统时间,那么在MySQL的主库和备库执行同一个这样的SQL语句,主库和备库的这一条数据肯定就不一致了。

    主备库不一致的问题必须要解决,两种解决方式:

    1、修复这种问题。

    2、不使用statement的语句级别复制,而是类似于oracle的,将数据变更记录下来,原样在备库执行一遍。

    第二种方式就是大家熟知的,binlog_format=ROW的方式。第一种就是now()不使用机器系统时间,而是取mysql的变量”TIMESTAMP”值。

    另外的类似的变量还包括insert_id(用于复制时,AUTO_INCREMENT的取值)等

    利用mysqlbinlog你可以看到每个binlog event都有一个时间值。

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    # at 441

    #121205 10:06:52 server id 5 end_log_pos 526 Query thread_id=5 exec_time=0 error_code=0

    SET TIMESTAMP=1354673212.982122/*!*/;

    BEGIN

    /*!*/;

    # at 526

    #121205 10:06:52 server id 5 end_log_pos 642 Query thread_id=5 exec_time=0 error_code=0

    use`test`/*!*/;

    SET TIMESTAMP=1354673212.982122/*!*/;

    insert into gguard values(3,now())

    /*!*/;

    # at 642

    #121205 10:06:52 server id 5 end_log_pos 669 Xid = 26

    COMMIT/*!*/;

    备库复制执行时,SQL thread在做每个insert或者其他操作前首先要执行SET TIMESTAMP这样的动作,保证now()函数在statement模式下在备库和主库一样。

    这里还有另外一种含义:sysdate()函数在statement模式下,主库和备库会不一致,也就是说sysdate在statement复制模式下是不安全的。

    那么怎么实现上面的SELECT NOW(),SYSDATE();查询出来的时间不一样列,你只需要在之前执行:

    1

    2

    SET TIMESTAMP=UNIX_TIMESTAMP('1999-01-01');

    SELECT NOW(),SYSDATE();

    体验now()和sysdate()的神秘吧

    注意:

    CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP()都是now()函数的同义词,不讨论。

    sysdate()没有同义词。

    如果你觉得now()函数就够了,你不需要每次都取当前的机器系统时间,那么你可以在MySQL启动时指定–sysdate-is-now,这样的话MySQL会把sysdate()当成now()的一个同义词。

    展开全文
  • NOW、CURRENT_TIMESTAMP和SYSDATE:都是返回系统的当前时间;mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();+---------------------+---------------------+---------------------+| NOW() | CURRENT_TIMEST....

    NOW、CURRENT_TIMESTAMP和SYSDATE:都是返回系统的当前时间;

    mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();

    +---------------------+---------------------+---------------------+

    | NOW() | CURRENT_TIMESTAMP() | SYSDATE() |

    +---------------------+---------------------+---------------------+

    | 2014-01-12 15:24:24 | 2014-01-12 15:24:24 | 2014-01-12 15:24:24 |

    +---------------------+---------------------+---------------------+

    1 row in set (0.06 sec)

    这三个函数返回的都是当前的时间,按上面的例子而言,好像功能是相同的;

    mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),

    -> SLEEP(2),

    -> NOW(),CURRENT_TIMESTAMP(),SYSDATE();

    +---------------------+---------------------+---------------------+----------+--

    -------------------+---------------------+---------------------+

    | NOW() | CURRENT_TIMESTAMP() | SYSDATE() | SLEEP(2) | N

    OW() | CURRENT_TIMESTAMP() | SYSDATE() |

    +---------------------+---------------------+---------------------+----------+--

    -------------------+---------------------+---------------------+

    | 2014-01-12 15:30:52 | 2014-01-12 15:30:52 | 2014-01-12 15:30:52 | 0 | 2

    014-01-12 15:30:52 | 2014-01-12 15:30:52 | 2014-01-12 15:30:54 |

    +---------------------+---------------------+---------------------+----------+--

    -------------------+---------------------+---------------------+

    1 row in set (2.00 sec)

    加入SLEEP函数,让其等待2秒,这时发现SYSDATE()和NOW(),CURRENT_TIMESTAMP()返回的时间是不同的; 原因:   CURRENT_TIMESTAMP是NOW的同义词,即两者是相同的;   SYSDATE函数返回的是执行到当前函数的时间,而NOW返回的是执行SQL语句时的时间;   对于NOW函数,不管是在SLEEP()函数之前或之后执行,返回的都是执行这条SQL语句时的时间;

    展开全文
  • 加法select sysdate,add_months(sysdate,12) from dual;--加1年select sysdate,add_months(sysdate,1) from dual;--加1月select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') fromdual; --加1星期select ...
  • 4、postgresql内核开发之 SYSDATE实现,点击前往 5、参考书籍:《PostgreSQL中文手册》 1、本文内容全部来源于开源社区 GitHub和以上博主的贡献,本文也免费开源(可能会存在问题,评论区等待大佬们的指正) 2、...
  • SYSDATE用来得到系统的当前日期SQL> select to_char(sysdate,dd-mm-yyyy day) from dual;TO_CHAR(SYSDATE,-----------------09-05-2004 星期日trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,...
  • MySQL获得当前日期+时间(date + time)函数sysdate() :sysdate() 日期时间函数跟 now() 类似,不同之处在于:now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值。看下面的例子就明白了:mys...
  • MySQL中时间函数NOW()和SYSDATE()的区别SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE()先查询了NOW()和SYSDATE(),然后sleep了3秒,再查询NOW()和SYSDATE()NOW()还有3个同义词,效果跟NOW()一样,而且都有2种形式...
  • sysdate()简单用法

    2021-03-22 13:00:21
    select a.*,trunc(months_between(sysdate,rzrq)) from rq a 查询出5年前受雇佣的员工信息: select * from rq where trunc(months_between(sysdate,rzrq))/5 >12 注意:在开发中,如果是日期函数,建议用此语句,...
  • select sysdate,add_months(sysdate,12) from dual; --加1年 select sysdate,add_months(sysdate,1) from dual; --加1月 select sysdate,to_char(sysdate+7,‘yyyy-mm-dd HH24:MI:SS’) from dual; --加1星期 select...
  • MySQL常用日期函数

    2021-01-28 00:28:10
    当前时间SELECTnow(),sysdate(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP (),sleep(1),now(),sysdate(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP ()结果大概如下: 用的最多的就是now()函数了,CURRENT_TIMESTAMP主要是用...
  • 如果在事务中获取当前运行时间应该使用sysdate函数。 关于时间的时区问题,utc时间和gmt时间基本一致。utc采用原子钟,理论上来说更准一些,实际上一般不用考虑。cst时区代表了4个标准时间,一般不建议使用,有时候...
  • 日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒 或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC. 年-月-日 小时:分钟:秒 ...
  • MySQL与Oracle函数对照

    2021-01-28 10:25:35
    ■系统日期·甲骨文SYSDATE·MySQL的NOW()■日期型→字符串类型转换(YYYY / MM / DD)·甲骨文:TO_DATE(TO_CHAR(SYSDATE),'YY-MM-DD“)·MySQL的:DATE_FORMAT(SYSDATE(),'%​​Y-%M-%D')■TRUNC(日期)·甲骨文...
  • 今天系统部发来一个问题,现场数据库的sysdate查询的时间,与数据库服务器的时间差13小时.于是怀疑是时区设置问题。要求现场分别在数据库服务器和应用服务器上执行sql语句select TZ_OFFSET('PRC'),CURRENT_DATE,...
  • mysql时间函数计算

    2021-03-03 19:18:24
    一、MySQL 获得当前日期时间 函数1.1 获得当前日期 + 时间(date + time)函数:now()mysql> select now();+———————+| now() |+———————+| 2008-08-08 22:20:46 |+———————+除了 now() 函数能...
  • 我查找资料都没有说明db2可不可以用sysdate,但是我的db2 9.7的版本的确可以用sysdate这个函数的。所以就ok了,所有的取得系统当前日期函数都改为sysdate,适用oracle和db2。 2、日期类型字段。对于数据类型为date的...
  • Mysql时间函数

    2021-03-13 21:32:10
    下文函数的datetime参数处既可以用时间字符串也可以用时间变量或表达式。一、获取当前时间下面几个方法等效,字符串环境输出格式为:YYYY-MM-DD HH:MM:SS,数字环境输出格式为:YYYYMMDDHHMMSSnow()current_timest.....
  • Oracle:时间类型

    2021-01-27 18:45:11
    取值为00~23 MI 一小时中的分钟 SS 一分钟中的秒 SSSS 从午夜开始过去的秒数 Oracle时间函数 sysdate 作用:sysdate函数用于返回当前系统的时间(返回date数据类型) 例2:查询 例2_1:插入 sql:insert into USER_...
  • 天萃荷净分享一篇运维DBA需求,创建含sysdate函数index案例1.模拟Oracle数据库环境创建表插入数据库[oracle@node1 ~]$ sqlplus chf/oracleplusSQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 9 16:27:19 ...
  • MySQL中的SYSDATE()函数用于根据函数的上下文以YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS.uuuuuu格式返回当前...示例1:使用SYSDATE函数获取当前日期和时间。SELECT SYSDATE() as CurrentDateAndTime ;输出:CurrentDa...
  • oracle select话语

    2021-05-07 07:27:58
    人为以函数加以转换 日期,字符,数据三者之间可以相互转换:日期字符数据 日期格式:YYYY 日期-->字符 select to_char(sysdate,'yyyy-mm-dd') ch from dual CH ---------- 2006-07-28 数字-->字符 to_char(number,'...
  • 展开全部mysql时间类型的用法,给你转一篇日期函数说明,你看看就知e68a8462616964757a686964616f31333262356662道了,股票知识,太高深了,不在研究范围之内MySQL 获得当前日期时间 函数1.1 获得当前日期+时间(date...
  • ORACLE学习笔记一

    2021-05-07 07:28:45
    substr 字符截取函数 select ename,substr(ename,1,3) from emp; --从第1个位置开始 显示3个字符 9. sysdate 系统时间 select sysdate from dual; select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; ...
  • AIX系统使用命令date得到的时间是正确的,但是Windows下的Oracle8.1.7客户端,使用SQL*PLUS连接到服务器,sysdate函数得到的日期却与系统时间相差了8个小时,当时一想估计是服务器系统时间是北京时间,而Oracle的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 49,156
精华内容 19,662
关键字:

sysdate函数