I am a parsing a file into a sqlite database that contains dates in the YYYY-MM-DD format. I want to store the entries into sqlite in such a way that I can sort the entries by date (strings not cutting it). What is the normal protocol for storing and ordering dates in sqlite? Should convert the dates into a number. Is there a way to convert YYYY-MM-DD dates into timestamps?
相关问题
- Correctly parse PDF paragraphs with Python
- R: eval(parse()) error message: cannot ope
- SQL/SQL-LITE - Counting records after filtering
- What SQLite NuGet package do I need
- How do I parse a .pls file using PHP? Having troub
If You want to Current TimeStamp then use
If You want toYYYY-MM-DD date TimeStamp then use
where %s=seconds since 1970-01-01
SQLite supports "DATE" in table creation. (More about that later.)
Values in the form yyyy-mm-dd sort correctly as either a string or a date. That's one reason yyyy-mm-dd is an international standard.
But SQLite doesn't use data types in the way most database workers expect it. Data storage is based on storage classes instead. For example, SQLite allows this.
It also allows different date "formats" (actually, values) in a single column. Its behavior is quite unlike standard SQL engines.
You don't have to do anything special to store a timestamp in a date column. (Although I'd rather see you declare the column as timestamp, myself.)
SQLite will try to do the Right Thing as long as you feed consistent data into it. And it will sort dates correctly if you use the standard format.
i have the date field store in this way DD/MM/YYYY. For sorting the date ( date field is a string ) i have to convert it before order it.
Instead of storing date in format "YYYY-MM-DD", store the time-stamp of that date and that will help you to sorting the table.