Menu
I am trying to work with dates in an sqlite database. I am storing my dates as timestamps, but when I use strftime() to format them to human readable dates I am getting back unxpected results.
Consider the following, I select the current timestamp:
SELECT strftime("%s","now"); 1281353727 Then I try to format a date using the timestamp that I know to represent now expecting to get back a human readable format of todays date:
SELECT strftime('%d - %m - %Y ', 1281353727); 01 - 04 - 3503 Instead I get the above result. Is this correct behaviour? am I doing something wrong?
Thanks in advance,
Kevin
823
Use the STRFTIME() function to format date\time\datetime data in SQLite. This function takes two arguments. The first argument is a format string containing the date/time part pattern. In our example, we use the format string '%d/%m/%Y, %H:%M'.
The SQLite datetime function is a very powerful function that can calculate a date/time value, and return it in the format 'YYYY-MM-DD HH:MM:SS'.
The SQLite function DATE('now') returns the current date as a text value. It uses the 'YYYY-MM-DD' format, where YYYY is a 4-digit year, MM is a 2-digit month, and DD is a 2-digit day of the month. This function takes one argument: the text 'now' indicates the current date and time.
The SQLite date() function is used to calculate the date and return it in the format 'YYYY-MM-DD'. The SQLite datetime() function is used to calculate a date/time value, and return it in the format 'YYYY-MM-DD HH:MM:SS'. The SQLite julianday() function returns the date according to julian day.
You need to convert the timestamp to datetime first:
SELECT strftime('%d - %m - %Y ', datetime(1281353727, 'unixepoch')) FROM Visits; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
