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:

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:
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;
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.


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

  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

  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

  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

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

  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

  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


  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..

  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

  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..

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

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

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

  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

  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

  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

  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..

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

  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

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

  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

  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

  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....................

  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


  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

  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

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

  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

  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

  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.

  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

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

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

  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

  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

  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

  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

  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


  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

  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

  41. it is an awesome article. i can enhance my knowledge from this article. thank you for sharing this.
    best selenium training in chennai

  42. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    java training in chennai | java training in bangalore

    java training in tambaram | java training in velachery

    java training in omr

  43. Useful Information :

    Looking for the Best [url= Digital Marketing Institute in Vijayawada ][/url] , We provide training on live projects, internship, Flexible Classes, Free Demo @ digitalinnovation.co.in

  44. It is better to engaged ourselves in activities we like. I liked the post. Thanks for sharing.
    python online training
    python training in OMR
    python training course in chennai

  45. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
    Selenium Training in Chennai | Selenium Training in Bangalore | Selenium Training in Pune | Selenium online Training

  46. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.

    Devops Training in pune
    DevOps online Training

  47. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa interview questions and answers
    automation anywhere interview questions and answers
    blueprism interview questions and answers
    uipath interview questions and answers
    rpa training in chennai

  48. I think this is the best article today about the future technology. Thanks for taking your own time to discuss this topic, I feel happy about that curiosity has increased to learn more about this topic. Keep sharing your information regularly for my future reference.
    selenium Training in Chennai
    Selenium Training Chennai
    ios training institute in chennai
    ios developer training in chennai
    Software testing training institutes
    Software Testing courses in chennai

  49. This comment has been removed by the author.

  50. Outstanding blog thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.
    Air Hostess Training in Chennai | Air Hostess Training Institute in Chennai | Air Hostess Academy in Chennai
    Air Hostess Courses in Chennai |Best Air Hostess Training Institute in Chennai | Cabin Crew Course in Chennai

  51. This comment has been removed by the author.

  52. Very well written blog and I always love to read blogs like these because they offer very good information to readers with very less amount of words....thanks for sharing your info with us and keep sharing.
    Python training in bangalore
    Python course in pune
    Python training in bangalore

  53. Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.
    excel advanced excel training in bangalore | Devops Training in Chennai

  54. Marvelous and fascinating article. Incredible things you've generally imparted to us. Much obliged. Simply keep making this kind out of the post.

    Oracle DBA training in Chennai
    Oracle DBA Training

  55. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!
    iosh course in chennai


Post a Comment

Popular posts from this blog

What's installed on my ec2

BootstrapX clickover