Tuesday, March 10, 2009

2 ways to get SQLite to put dates into columns

2 ways to get SQLite to put dates into a column.
  1. insert into mytable values( null, datetime('now') );
  2. insert into mytable values( null, strftime('%s', 'now'));
The first one inserts a row somewhat like this:
1|2009-03-10 18:47:46

The second inserts an unix timestamp:
2|1236711411

It might be best to use that unix timestamp with an integer column type for dates since SQLite doesn't support a datetime one.

It makes comparisons and ordering much easier:
select * from dt where lu > strftime('%s', '2009-03-10');

Output of:
id|lu
6|1236643200
2|1236711411
3|1236711516
4|1236711518
5|1236711519
But the formatting is pretty ugly. :-/

Hey what about formatting it within the select with the SQLite datetime function:
select id, datetime(lu, 'unixepoch') lu from dt order by lu;
id|lu
6|2009-03-10 00:00:00
2|2009-03-10 18:56:51
3|2009-03-10 18:58:36
4|2009-03-10 18:58:38
5|2009-03-10 18:58:39
Better but having to add that to each select is kind of a pain. I like adding a new column better:
select *, datetime(lu, 'unixepoch') date from dt order by lu;

Alright done playing now... It would be probably be good to add a language extension for a new function called 'now()'. Some other day :)

For more datetime SQLite information, see the SQLite date reference.

68 comments:

  1. Hi, Your blog is really very informative and useful for me. Thanks for sharing this valuable blog.
    Regards..
    Unix Training

    ReplyDelete
  2. A debt of gratitude is in order for your instructive article on programming testing. Your post helped me to comprehend the future and vocation prospects in programming testing. Continue upgrading your web journal with such wonderful article. Ccna Training in Chennai

    ReplyDelete
  3. hi,we have to sharing now helpful information...i have to learing for lot of information this article.Keep well awesome article..

    Informatica Training in Chennai

    ReplyDelete
  4. Extraordinary post I might want to thank you for the endeavors you have made in composing this intriguing and learned article.

    dot net training in chennai | Ccna Training in Chennai

    ReplyDelete
  5. Thanks for Sharing this. Just add some more information about altering of table... http://www.trainingbangalore.in/android-training-in-bangalore.html

    ReplyDelete
  6. Very useful information..Explain me how to alter the column values in a different table?@ Oracle DBA Training in Bangalore | PHP Training in Bangalore

    ReplyDelete
  7. your adding table to sql is fine . i have doubt in what is the different between sql and plsql? how to insert plsql table ? how do fine to same name in one table to another in sql method? selenium Training in Bangalore |
    Oracle Training in Bangalore

    ReplyDelete

  8. Oracle DBA Training in Chennai
    Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

    ReplyDelete
  9. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Websphere Training in Chennai

    ReplyDelete
  10. Data warehousing Training in Chennai
    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

    ReplyDelete
  11. Selenium Training in Chennai
    Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    ReplyDelete
  12. Oracle Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  13. SAP Training in Chennai
    This post is really nice and informative. The explanation given is really comprehensive and informative..

    ReplyDelete
  14. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic
    Android Training In Chennai In Chennai

    ReplyDelete
  15. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Unix Training In Chennai

    ReplyDelete
  16. I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    SalesForce Training in Chennai

    ReplyDelete
  17. There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

    ReplyDelete
  18. This is really an awesome article. Thank you for sharing this.It is worth reading for everyone. Visit us:
    Oracle Training in Chennai

    ReplyDelete
  19. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Oracle DBA Training in Chennai

    ReplyDelete
  20. Wonderful tips, very helpful well explained. Your post is definitely incredible. I will refer this to my friend.SalesForce Training in Chennai

    ReplyDelete
  21. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.Oracle RAC Training in Chennai

    ReplyDelete
  22. Thank you for the useful post. It helps a lot in my training. I share your blog with my students. Keep posting more.
    Selenium Training in Chennai

    ReplyDelete
  23. Latest Govt Bank Jobs 2016

    It was helpful and will really help me in building my new blog traffic and increasing my readers per day....................

    ReplyDelete
  24. Great the given information and to anlyze that post,i get new information because of all given information very excellent and to developed for you.Thank you so much shared that valuable information.


    dotnet training in chennai

    ReplyDelete

  25. The training was very great and more information to get after end of course,Then the more offers are provided on the training times.
    INFORMATICA Training in Chennai

    ReplyDelete
  26. This blog explains the details of most popular technological details. This helps to learn about what are all the different method is there. And the working methods all of that are explained here. Informative blog.
    Informatica Training in Chennai

    ReplyDelete
  27. very informative blog. Helps to gain knowledge about new concepts and techniques. Thanks for posting information in this blog
    CCNA Training in Chennai

    ReplyDelete
  28. Thanks a lot for letting me a chance to visit your any pointers. Your article about web design is really impressed me very much.Contact Us Page

    ReplyDelete
  29. Thanks a lot for letting me a chance to visit your any pointers. Your article about web design is really impressed me very much.Contact Us Page

    ReplyDelete
  30. SQL is the nice topic which you are discussed..
    Best SEO training in hyderabad all modules are clearly solved and practice with guide.seo training in hyderabadAnd clearly getting knowledge of seo.

    ReplyDelete
  31. A good blog. Thanks for sharing the information. It is very useful for my future. keep sharing
    red ball 2 | duck life 2 | happy wheels | Red Ball | Red ball 3 | Flash Games| Tank trouble

    ReplyDelete
  32. Useful Information
    one and only affiliate agency in south INDIA, earn money online from affiliate network in india

    ReplyDelete
  33. Freshers jobs way provide freshers jobs ,walkins,direct recruitment,openings for freshers and experienced and recruiting experienced and freshers Walkins

    ReplyDelete

  34. useful information

    Online recruitment for bank jobs and government jobs and you can get Notification and application to apply online for bank jobs and govt jobs

    ReplyDelete
  35. Useful information:
    General Awareness and Current Affairs with Latest Current Affairs Quiz, GK Updates, General Knowledge Last 6 Months Current Affairs, Placement Papers, Model Papers, Interview Questions @ examvalue.com

    ReplyDelete
  36. useful information

    Online recruitment for bank jobs and government jobs and you can get Notification and application to apply online for bank jobs and govt jobs

    ReplyDelete
  37. Great post. Happy to visit your blog. Thanks for sharing.

    html training in chennai

    ReplyDelete
  38. Helpful Information

    Telugu70mm.com Provides Latest Telugu Movie Reviews , Telugu Political News and Other Latest Telugu Movie News

    ReplyDelete
  39. Helpful Information:

    Telugu Cinema Contains Telugu Cinema News, Telugu Movie Reviews and many more @ http://www.telugucinema.com/

    ReplyDelete
  40. Updating with the latest technology and implementing it is the only way to survive in our niche. Thanks for making me this article. You have done a great job by sharing this content in here. Keep writing article like this.
    Angularjs training in chennai | Angularjs course in Chennai

    ReplyDelete
  41. Java Online Training Java Online Training Java Online Training Java Online Training Java Online Training Java Online Training

    Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai

    ReplyDelete
  42. I have read your blog its very attractive and impressive. I like it your blog.

    Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

    Java Online Training Java Online Training Core Java 8 Training in Chennai Core java 8 online training JavaEE Training in Chennai Java EE Training in Chennai

    ReplyDelete
  43. Its really helpfull.., thanks for sharing this.

    Papa jobs Providing latest information about Current affairs, Bank Recruitment, Government jobs, Bank jobs, IT jobs., check this link papa jobs i hope this is very use full to you and all the very best Guys.

    ReplyDelete
  44. Nice information for job seekers. Thanks for sharing the awesome information , i also recommend online Notification

    ReplyDelete
  45. Very Good Information and i also recommend Freshers job way ; as they are leading sites provides job information

    ReplyDelete
  46. Thanks , you are giving very important information for many job seekers. In my experience , i got job through Recruitment Voice and according to me it is also one of the best site.

    ReplyDelete
  47. Great post.
    It really helped me to learn something new. So thanks.

    Microsoft azure training in Bangalore

    ReplyDelete
  48. It was really a nice article. I would like to thank you for the sharing. About the oracle Online Training is one of the leading Online Training institute.
    Oracle fusion financials training

    ReplyDelete
  49. CALFRE is a leading oracle fuison Financials Training Institute. we offer this course through online we have great experience in succeeding students through online courses. we can calculate our performance through their honest comments in our sites in supporting our services. we have referral program so candidates can earn money through referral. you can share your live experience with other can generate you some money.


    Oracle fusion Financials Training in hyderabad

    Oracle Fusion Financials online Training in hyderabad

    ReplyDelete
  50. Nice Information
    one and only A Digital Marketing Agency in INDIA affiliate network in india

    ReplyDelete
  51. Too good piece of information,your blog gives the best and the most interesting information. This is just the kind of information students had been looking for.There are so many Online training institutes in Bangalore and Intellipaat is the best Sql Online Training institute

    ReplyDelete
  52. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete