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.

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. extremely useful blog. Increases learning about new ideas and procedures. A debt of gratitude is in order for posting data in this blog
    Article Submission sites | Education | Technology | Latest Updates

    ReplyDelete
  35. Thanks for taking time to discuss about this technology. I love to learn more about this topic. If possible. as you gain experience update your blog with more information? It is extremely helpful for me.
    Core Java Training
    best java training institute in chennai with placement

    ReplyDelete
  36. Thank you for this post!! I have just discovered your blog recently and I really like it! I will definitely try some of your insights
    big data training institute in chennai
    big data classes in chennai

    ReplyDelete
  37. Thanks for sharing this interesting blog with us.My pleasure to being here on your blog..I wanna come beck here for new post from your site.
    Dot net courses in chennai 
    .net training in chennai

    ReplyDelete
  38. Wonderful post!!Thank you for sharing this info with us.
    Keep updating I would like to know more updates on this topic
    Very useful content, I would like to suggest this blog to my friends.

    Informatica Training institutes in Chennai


    Informatica Training chennai

    ReplyDelete


  39. This was an nice and amazing and the given contents were very useful and the precision has given here is good.
    Selenium Training Institute in Chennai

    ReplyDelete
  40. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much.
    html Training in chennai

    ReplyDelete

Post a Comment

Popular posts from this blog

BootstrapX clickover

Bootstrap (by Twitter) loading in page content for Popovers