Re: [Cppcms-users] sqlite3 CURRENT_TIMESTAMP compare to date_time

[Artyom B. <art...@ya...>]

 
> Hi,
> 
> Just a quick question.
> 
> Given a column in an sqlite database is set to DEFAULT CURRENT_TIMESTAMP,
> which is stored in UTC, and not local time and an SQL SELECT such as
> 
> SELECT * FROM table WHERE created_at < ?
> 
> and a locale::date_time object representing current time, should date_time
> be first converted to UTC before being sent to the SQL query?
> 

Yes... if you use cppdb you can do it by

   sql << "select ... ?" << booster::ptime::universal_time(booster::ptime::now()) << ...




> ie.
> 
> ostringstream os;
> 
> os << as::ftime("%Y-%m-%d %H:%M:S") << 
> as::timezone("UTC") << now;
> 
> 1) Is this correct?
> 

Yes it is, however it is better to use as::gmt rather that as::timezone("UTC");

> 2) What if the column is not a timestamp but a date, and the query is
> comparing date with date_time. Should conversion to UTC be still done?

This is good question.

The problem is in what time zone you refer to date. Usually
date is meaningless out of timezone scope.

What does 2011-03-04 mean? If it is in GMT time in certain time zones
it would be from 7 AM to 7 AM 

So you likely do not want to store dates in UTC, if you store
dates in UTC you likely need to store entire timestamp.

> 
> Thanks,
> Petr

Artyom