守望者--AIR技术交流

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
热搜: ANE FlasCC 炼金术
查看: 585|回复: 0

[SQLite] AIR SQLite中date处理的一个好法方法

[复制链接]
  • TA的每日心情
    擦汗
    2018-4-10 15:18
  • 签到天数: 447 天

    [LV.9]以坛为家II

    1742

    主题

    2094

    帖子

    13万

    积分

    超级版主

    Rank: 18Rank: 18Rank: 18Rank: 18Rank: 18

    威望
    562
    贡献
    29
    金币
    51753
    钢镚
    1422

    开源英雄守望者

    发表于 2015-1-10 21:48:03 | 显示全部楼层 |阅读模式
    Date range query using SQLite and AIR
    I decided I would investigate more on Dates and SQLite. As you know I had some trouble when trying to extract a date range from my SQLite database because Flash and SQLite don’t save dates in the same format.  Flash saves it in UTC format and SQLite as a Julian day.
    Looking around a bit I found on the php website some information about how to do the conversion from Julian day to UTC.  All I had to do was to turn the formula around. But that didn’t work because the Date in Flash is also offested to account for the timezone of where the computer is. Once that was solved, everything worked fine. Here is a function that will do the conversion:

    1. function UTCToJulianDay(newDate:Date):Number{
    2.   return ( newDate.time - (newDate.getTimezoneOffset()*60000))  / 86400000) + 2440587.5)
    3. }
    复制代码
    The time method of Date object return milliseconds so we have to change everything in milliseconds. The getTimezoneOffset  method returns minutes so by multiplying by 60000 you get its millisecond equivalent. 86400000 is the number of milliseconds in a day and 2440587.5 is the julian day at 1/1/1970 0:00 UTC.
    So using this function will enable you to use the greater than, lesser than and BETWEEN operator in SQLite when you want to search for date ranges. Here are some example of SQL query you might do:
    1. sqlStmt.text = "SELECT * FROM main.Entry WHERE dateF >; " + UTCToJulianDay(new Date());
    2. //this will return all entries where dateF is bigger than now

    3. var date1:Date = new Date(2009, 2, 3) ; //March 3rd 2009
    4. var date2:Date = new Date(2009, 3, 3); //April 3rd 2009

    5. sqlStmt.text = "SELECT * FROM main.Entry WHERE dateF BETWEEN " + UTCToJulianDay(date1) + " AND " + UTCToJulianDay(date2) ;
    6. //this will return all entries  Between March 3rd and April 3rd
    复制代码


    守望者AIR技术交流社区(www.airmyth.com)
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    
    关闭

    站长推荐上一条 /4 下一条

    QQ|手机版|Archiver|网站地图|小黑屋|守望者 ( 京ICP备14061876号

    GMT+8, 2019-10-19 15:43 , Processed in 0.044182 second(s), 33 queries .

    守望者AIR

    守望者AIR技术交流社区

    本站成立于 2014年12月31日

    快速回复 返回顶部 返回列表