Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I run a small crawler on a virtual ubuntu server, initiated by crontab hourly. Datetime is inserted by defaulting the date filed to TIMESTAMP DEFAULT CURRENT_TIMESTAMP.

Table creation looks like this:

CREATE TABLE links (page TEXT, link TEXT, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(page,link));

The datetime gets stored fine, but it one hour off (one hour behind) Norwegian time (GMT +1). The server is located where-ever, I just need it to be on GMT+1.

By typing datein the ssh session I get:

Wed Dec 19 17:26:02 CET 2012

and that is correct (just now).

So where does sqlite3 get it's time from? What must I do to set the time so that sqlite3 gets the time right?

share|improve this question

1 Answer 1

sqlite stores dates in utc. To see a date in localtimezone use the localtime modifier.

select datetime(date,'localtime') from links;
share|improve this answer
    
Ok. So sqlite stores dates in utc.. in plain text that's just "Western European Time", right? Can I make sqlite store in localtime? (in my case +1 hour) –  Eiriks Dec 20 '12 at 11:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.