原创 2011年10月16日 12:06:03








private final static String CREATE_INCOME_RECORDS_TABLES="create table income_records(_id integer primary key autoincrement,"+
							"income_money real not null,"+
							"income_category text not null,"+
							"income_type text not null,"+
							"income_remarks text,"+
							"income_time datetime DEFAULT current_date not null);";


  1. date(timestring, modifier, modifier, ...)
  2. time(timestring, modifier, modifier, ...)
  3. datetime(timestring, modifier, modifier, ...)
  4. julianday(timestring, modifier, modifier, ...)
  5. strftime(format, timestring, modifier, modifier, ...)

 The strftime() function also takes a format string as its first argument.

The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the Julian day- the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar). The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in thestrftime() function from the standard C library plus two new substitutions, %f and %J. The following is a complete list of valid strftime() substitutions:

day of month: 00
fractional seconds: SS.SSS
hour: 00-24
day of year: 001-366
Julian day number
month: 01-12
minute: 00-59
seconds since 1970-01-01
seconds: 00-59
day of week 0-6 with Sunday==0
week of year: 00-53
year: 0000-9999

Notice that all other date and time functions can be expressed in terms of strftime():

Equivalent strftime()
strftime('%Y-%m-%d', ...)
strftime('%H:%M:%S', ...)
strftime('%Y-%m-%d %H:%M:%S', ...)
strftime('%J', ...)

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

Time Strings

A time string can be in any of the following formats:

  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as a floating point value.


The time string can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

  1. NNN days
  2. NNN hours
  3. NNN minutes
  4. NNN.NNNN seconds
  5. NNN months
  6. NNN years
  7. start of month
  8. start of year
  9. start of day
  10. weekday N
  11. unixepoch
  12. localtime
  13. utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. Note that "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 10675199167).

The "localtime" modifier (12) assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC. If the prior string is not in localtime, then the result of "utc" is undefined.


Compute the current date.

SELECT date('now');

Compute the last day of the current month.

SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.

SELECT strftime('%s','now');

Compute the number of days since the signing of the US Declaration of Independence.

SELECT julianday('now') - julianday('1776-07-04');

Compute the number of seconds since a particular moment in 2004:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Compute the date of the first Tuesday in October for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

SELECT (julianday('now') - 2440587.5)*86400.0;

Caveats And Bugs

The computation of local time depends heavily on the whim of politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime_r() is used to assist in the calculation of local time. The localtime_r() C function normally only works for years between 1970 and 2037. For dates outside this range, SQLite attempts to map the year into an equivalent year within this range, do the calculation, then map the year back.

These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julidan day numbers 1721059.5 through 5373484.5). For dates outside that range, the results of these functions are undefined.

Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the new 2007 DST rules to all previous years as well. Vista does somewhat better getting results correct back to 1986, when the rules were also changed.

All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration.


select * from schedlue where 

datetime( start_time)>=datetime('2011-08-01') and datetime(end_time)<=datetime('2011-09-01')



Jquery取出来的时间是国际化的时间。有时不符合我们的业务需求,我们可以根据Jquery提供的方法对时间进行截取组合。 var date=new Date();          var str...
  • u012312373
  • u012312373
  • 2015年06月24日 19:23
  • 603


开发工作,在于不断积累,总结经验,持续学习。 最近项目中有这样的需求: 1、删除当前月份前三个月消息(如本月是10月删除7、8、9三个月的消息只保存本月消息)。 2、更新非本月消息为已读消息(如...
  • pys214371027
  • pys214371027
  • 2016年10月13日 14:53
  • 3751


在学JavaSE部分,我相信老师肯定会讲到对时间操作的API有Date, SimpleDateFormat, Calendar,这三个类。我个人比较 实用性主义,太多的理论性东西不太会描述 ,水平有限...
  • AdrainHuang
  • AdrainHuang
  • 2015年12月25日 00:41
  • 179


实际编码中,对时间与日期的操作是非常常见的需求。写了个比较简单的类,来满足部分对时间相关的操作。后续可以根据需求再添加相关功能。import java.text.ParseException; imp...
  • bitcarmanlee
  • bitcarmanlee
  • 2016年12月16日 12:56
  • 511


1.日期时间加减$time="2005-12-20"通过函数转变为$time="2005-12-21两种方法a.unix时间戳b.使用date()$time = date("Y-m-d",(strto...
  • yuelengxin
  • yuelengxin
  • 2006年01月23日 19:01
  • 852


var time_String = "2015-11-22 15:20:35:88";//年-月-日 时:分:秒:毫秒 var time_Date = new Date(time_String);//...
  • a392266126
  • a392266126
  • 2016年12月01日 15:01
  • 141


import java.util.Date;    import java.util.Calendar;   import java.text.SimpleDateFormat;   publi...
  • u012365843
  • u012365843
  • 2017年05月08日 18:04
  • 382


TextBox1.Text = DateTime.Today.ToString("yyyy年M月d日");//点第一个BUTTON时TextBox1.Text = DateTime.Today.Add...
  • lanlandechong
  • lanlandechong
  • 2007年09月15日 09:28
  • 411


先附加数据库: attach database '/bak/ipguard.dat' as b_conf
  • u012846431
  • u012846431
  • 2014年10月28日 14:17
  • 900

学习笔记:Android SQLite,并实现SQLite基本CRUD操作的Demo

一、背景       Android系统内置有SQLite,用户可以在本地存放大量较为复杂的关系型数据,相较于File操作和SharedPreferences只能存放简单的数据,SQLite具有更大的...
  • jinxin_cuit
  • jinxin_cuit
  • 2016年09月21日 15:35
  • 1123