firebird当前时间获取

最新推荐文章于 2022-08-24 19:44:03 发布

gyp_499019480

最新推荐文章于 2022-08-24 19:44:03 发布

阅读量4.2k

点赞数

分类专栏：数据库文章标签：获取时间

数据库专栏收录该内容

6 篇文章

订阅专栏

日期时间操作
FB中获得时间的操作符：
CURRENT_DATE : 当前日期
CURRENT_TIME : 当前时间
CURRENT_TIMESTAMP: 当前的日期时间,也称时间戳
从时间类型中获得信息的操作符：
EXTRACT(.. FROM ..)
Select EXTRACT(.. FROM ..) from RDB$DATABASE
如果返回值不存在时间值中会报错.
时间类型中的年份 EXTRACT(YEAR from CURRENT_TIMESTAMP); EXTRACT(YEAR from CAST('2005-8-1' as Date))=2005
        月份 EXTRACT(MONTH from CURRENT_TIMESTAMP); EXTRACT(MONTH from CAST('2005-8-1' as Date))=8
        日期 EXTRACT(DAY from CURRENT_TIMESTAMP); EXTRACT(DAY from CAST('2005-8-1' as Date))=1
        天数 EXTRACT(YEARDAY from CURRENT_TIMESTAMP) ;EXTRACT(YEARDAY from CAST('2005-8-1' as Date))=212
        星期几 EXTRACT(YEARDAY from CURRENT_TIMESTAMP); EXTRACT(DAY from CAST('2005-8-1' as Date))=1
CAST('Now' as Timestamp) 相当于 Current_Timestamp
CAST('ToDay' as Date)   今天的日期
CAST('yesterday' as Date') 昨天的日期

获取当前时间的方法：

1.创建一个存储过程执行

create procedure "GET_CURRENT_TIMESTAMP" returns ("CUR_TIME" TIMESTAMP) as
begin
CUR_TIME = CURRENT_TIMESTAMP;
end

2.利用以下 SQL 命令取得現在時間：

select distinct CURRENT_TIMESTAMP from "資料表名稱"

"資料表名稱"請選擇筆數很少的資料表，執行速度會比較快。

写UDF的例子,希望能抛砖引玉

unit DateTimeFunction;

interface

uses
SysUtils,Windows;//,iberror_h,ibase_h;

type

TM = record
  tm_sec : integer; // Seconds
  tm_min : integer; // Minutes
  tm_hour : integer; // Hour (0--23)
  tm_mday : integer; // Day of month (1--31)
  tm_mon : integer; // Month (0--11)
  tm_year : integer; // Year (calendar year minus 1900)
  tm_wday : integer; // Weekday (0--6) Sunday = 0)
  tm_yday : integer; // Day of year (0--365)
  tm_isdst: integer; // 0 if daylight savings time is not in effect)
end;

PTM = ^TM;

ISC_TIMESTAMP = record
  timestamp_date : LongInt;
  timestamp_time : ULong;
end;

PISC_TIMESTAMP = ^ISC_TIMESTAMP;

const IBASE_DLL = 'gds32.dll';

//格式化时间函数
function DL_FormatDate(AFMT: PChar; var ib_date: ISC_TIMESTAMP): PChar;
//根据月份返回中文月份
function DL_GetMonthNameCN(var AMonth: Integer): PChar; cdecl;
//返回中文星期几
function DL_GetWeekNameCN(var AWeek: Integer): PChar; cdecl;
//返回版本号
function DL_Version(): PChar; cdecl;
implementation

procedure isc_encode_timestamp (tm_date: PTM;
                   ib_date: PISC_TIMESTAMP);
                stdcall; external IBASE_DLL;

procedure isc_decode_timestamp (ib_date: PISC_TIMESTAMP;
                 tm_date: PTM);
                stdcall; external IBASE_DLL;

procedure isc_decode_sql_date (var ib_date: LongInt;
                 tm_date: PTM);
                stdcall; external IBASE_DLL;

procedure isc_encode_sql_date (tm_date: PTM;
                 var ib_date: LongInt);
                stdcall; external IBASE_DLL;

procedure isc_decode_sql_time (var ib_date: ULong;
                 tm_date: PTM);
                stdcall; external IBASE_DLL;

procedure isc_encode_sql_time (tm_date: PTM;
                 var ib_date: ULong);
                stdcall; external IBASE_DLL;

//function isc_database_info(status_vector: PISC_STATUS;
//  db_handle: PISC_DB_HANDLE; item_list_buffer_length: Short;
//  item_list_buffer: PChar; result_buffer_length: Short;
//  result_buffer: PChar): ISC_STATUS; stdcall; external IBASE_DLL;

function DL_FormatDate(AFMT: PChar; var ib_date: ISC_TIMESTAMP): PChar;
var
  tm_date: TM;
  rs: string;
  d: TDateTime;
  sFMT: string;
begin
//select formatdate('yyyy',current_timestamp) from baseinfo rows 1
   isc_decode_timestamp(@ib_date,@tm_date);
   tm_date.tm_year := tm_date.tm_year + 1900;
   tm_date.tm_mon := tm_date.tm_mon + 1;
   tm_date.tm_yday := tm_date.tm_yday + 1;

   rs := Format('%d-%d-%d %d:%d:%d',[tm_date.tm_year,tm_date.tm_mon,tm_date.tm_mday,
                 tm_date.tm_hour,tm_date.tm_min,tm_date.tm_sec]);
   try
    d := StrToDateTime(rs);
    sFMT := LowerCase(AFMT);
    if sFMT='' then
      rs := FormatDateTime('YYYY-MM-DD',d)
    else if sFMT = 'year' then
      rs := IntToStr(tm_date.tm_year)
    else if sFMT = 'month' then
      rs := DL_GetMonthNameCN(tm_date.tm_mon)
    else if sFMT = 'day' then
      rs := FormatDateTime('dd',d)
    else if sFMT = 'hour' then
      rs := IntToStr(tm_date.tm_hour)
    else if sFMT = 'minute' then
      rs := IntToStr(tm_date.tm_min)
    else if sFMT = 'weekday' then //weekday (dw) 日期部分返回星期几（星期天、星期一等）。
      rs := DL_GetWeekNameCN(tm_date.tm_wday)
    else if sFMT = 'dayofweek' then
      rs := DL_GetWeekNameCN(tm_date.tm_wday)
    else if sFMT = 'week' then  //当月第几周
      rs := IntToStr(tm_date.tm_wday)
    else if sFMT = 'weekofyear' then  //当年第几周
      rs := IntToStr(tm_date.tm_yday div 7)
    else if sFMT = 'quarter' then //季度
    begin
      case tm_date.tm_mon of
        1,2,3: rs := '第一季度';
        4,5,6: rs := '第二季度';
        7,8,9: rs := '第三季度';
        10,11,12: rs := '第四季度';
      end;
    end
    else if sFMT = 'dayofyear' then //一年中的第几天
      rs := IntToStr(tm_date.tm_yday)
    else
      rs := FormatDateTime(sFMT,d);
   except
    rs := '';
   end;

   Result := PChar(rs);
end;

function DL_GetMonthNameCN(var AMonth: Integer): PChar; cdecl;
begin
  case AMonth of
   1: Result := '一月';
   2: Result := '二月';
   3: Result := '三月';
   4: Result := '四月';
   5: Result := '五月';
   6: Result := '六月';
   7: Result := '七月';
   8: Result := '八月';
   9: Result := '九月';
   10: Result := '十月';
   11: Result := '十一月';
   12: Result := '十二月';
   else Result := '';
  end;
end;

function DL_GetWeekNameCN(var AWeek: Integer): PChar; cdecl;
begin
  case AWeek of
   0: Result := '星期日';
   1: Result := '星期一';
   2: Result := '星期二';
   3: Result := '星期三';
   4: Result := '星期四';
   5: Result := '星期五';
   6: Result := '星期六';
   7: Result := '星期日';
  end;
end;

function DL_Version(): PChar; cdecl;
{var
Status_vector: ISC_STATUS_VECTOR;
Buffer: array[0..1023] of Char;
DBInfo: Char;
FDBHandle: Integer;
s: string;}
begin
{ FDBHandle := 0;
DBInfo := Char(isc_info_version);
isc_database_info(@Status_Vector, @FDBHandle, 1, @DBInfo,
  SizeOf(buffer), Buffer);

SetLength(s, integer(buffer[4]));
Move(Buffer[5], Result[1], Integer(Buffer[4]));
Result := PChar(s);}
Result := '1.0.0';
end;

end