Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
[FEATURE][expressions] Add make_date, make_time and make_datetime fun…
…ctions

These functions allow for direct creation of date/time values. Previously
this was only possible by going through the to_datetime/to_date/to_time
functions, which are string based and accordingly frustrating/inefficient
to use when you have numeric date/time component values.
  • Loading branch information
nyalldawson committed May 7, 2020
1 parent 7a172b5 commit c2715d7
Show file tree
Hide file tree
Showing 5 changed files with 122 additions and 0 deletions.
13 changes: 13 additions & 0 deletions resources/function_help/json/make_date
@@ -0,0 +1,13 @@
{
"name": "make_date",
"type": "function",
"description": "Creates a date value from year, month and day numbers.",
"arguments": [
{"arg":"year","description":"Year number. Years 1 to 99 are interpreted as is. Year 0 is invalid."},
{"arg":"month","description":"Month number, where 1=January"},
{"arg":"day", "description":"Day number, beginning with 1 for the first day in the month"}
],
"examples": [
{ "expression":"make_date(2020,5,4)", "returns":"date value 2020-05-04"}
]
}
16 changes: 16 additions & 0 deletions resources/function_help/json/make_datetime
@@ -0,0 +1,16 @@
{
"name": "make_datetime",
"type": "function",
"description": "Creates a datetime value from year, month, day, hour, minute and second numbers.",
"arguments": [
{"arg":"year","description":"Year number. Years 1 to 99 are interpreted as is. Year 0 is invalid."},
{"arg":"month","description":"Month number, where 1=January"},
{"arg":"day", "description":"Day number, beginning with 1 for the first day in the month"},
{"arg":"hour", "description":"Hour number"},
{"arg":"minute", "description":"Minutes"},
{"arg":"second", "description":"Seconds (fractional values include milliseconds)"}
],
"examples": [
{ "expression":"make_datetime(2020,5,4,13,45,30.5)", "returns":"datetime value 2020-05-04 13:45:30.500"}
]
}
13 changes: 13 additions & 0 deletions resources/function_help/json/make_time
@@ -0,0 +1,13 @@
{
"name": "make_time",
"type": "function",
"description": "Creates a time value from hour, minute and second numbers.",
"arguments": [
{"arg":"hour", "description":"Hour number"},
{"arg":"minute", "description":"Minutes"},
{"arg":"second", "description":"Seconds (fractional values include milliseconds)"}
],
"examples": [
{ "expression":"make_time(13,45,30.5)", "returns":"time value 13:45:30.500"}
]
}
69 changes: 69 additions & 0 deletions src/core/expression/qgsexpressionfunction.cpp
Expand Up @@ -1103,6 +1103,60 @@ static QVariant fcnToDateTime( const QVariantList &values, const QgsExpressionCo
return QVariant( datetime );
}

static QVariant fcnMakeDate( const QVariantList &values, const QgsExpressionContext *, QgsExpression *parent, const QgsExpressionNodeFunction * )
{
const int year = QgsExpressionUtils::getIntValue( values.at( 0 ), parent );
const int month = QgsExpressionUtils::getIntValue( values.at( 1 ), parent );
const int day = QgsExpressionUtils::getIntValue( values.at( 2 ), parent );

const QDate date( year, month, day );
if ( !date.isValid() )
{
parent->setEvalErrorString( QObject::tr( "'%1-%2-%3' is not a valid date" ).arg( year ).arg( month ).arg( day ) );
return QVariant();
}
return QVariant( date );
}

static QVariant fcnMakeTime( const QVariantList &values, const QgsExpressionContext *, QgsExpression *parent, const QgsExpressionNodeFunction * )
{
const int hours = QgsExpressionUtils::getIntValue( values.at( 0 ), parent );
const int minutes = QgsExpressionUtils::getIntValue( values.at( 1 ), parent );
const double seconds = QgsExpressionUtils::getDoubleValue( values.at( 2 ), parent );

const QTime time( hours, minutes, std::floor( seconds ), ( seconds - std::floor( seconds ) ) * 1000 );
if ( !time.isValid() )
{
parent->setEvalErrorString( QObject::tr( "'%1-%2-%3' is not a valid time" ).arg( hours ).arg( minutes ).arg( seconds ) );
return QVariant();
}
return QVariant( time );
}

static QVariant fcnMakeDateTime( const QVariantList &values, const QgsExpressionContext *, QgsExpression *parent, const QgsExpressionNodeFunction * )
{
const int year = QgsExpressionUtils::getIntValue( values.at( 0 ), parent );
const int month = QgsExpressionUtils::getIntValue( values.at( 1 ), parent );
const int day = QgsExpressionUtils::getIntValue( values.at( 2 ), parent );
const int hours = QgsExpressionUtils::getIntValue( values.at( 3 ), parent );
const int minutes = QgsExpressionUtils::getIntValue( values.at( 4 ), parent );
const double seconds = QgsExpressionUtils::getDoubleValue( values.at( 5 ), parent );

const QDate date( year, month, day );
if ( !date.isValid() )
{
parent->setEvalErrorString( QObject::tr( "'%1-%2-%3' is not a valid date" ).arg( year ).arg( month ).arg( day ) );
return QVariant();
}
const QTime time( hours, minutes, std::floor( seconds ), ( seconds - std::floor( seconds ) ) * 1000 );
if ( !time.isValid() )
{
parent->setEvalErrorString( QObject::tr( "'%1-%2-%3' is not a valid time" ).arg( hours ).arg( minutes ).arg( seconds ) );
return QVariant();
}
return QVariant( QDateTime( date, time ) );
}

static QVariant fcnCoalesce( const QVariantList &values, const QgsExpressionContext *, QgsExpression *, const QgsExpressionNodeFunction * )
{
for ( const QVariant &value : values )
Expand Down Expand Up @@ -5738,6 +5792,21 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
<< new QgsStaticExpressionFunction( QStringLiteral( "epoch" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "date" ) ), fcnEpoch, QStringLiteral( "Date and Time" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "datetime_from_epoch" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "long" ) ), fcnDateTimeFromEpoch, QStringLiteral( "Date and Time" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "day_of_week" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "date" ) ), fcnDayOfWeek, QStringLiteral( "Date and Time" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "make_date" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "year" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "month" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "day" ) ),
fcnMakeDate, QStringLiteral( "Date and Time" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "make_time" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "hour" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "minute" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "second" ) ),
fcnMakeTime, QStringLiteral( "Date and Time" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "make_datetime" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "year" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "month" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "day" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "hour" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "minute" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "second" ) ),
fcnMakeDateTime, QStringLiteral( "Date and Time" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "lower" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "string" ) ), fcnLower, QStringLiteral( "String" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "upper" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "string" ) ), fcnUpper, QStringLiteral( "String" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "title" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "string" ) ), fcnTitle, QStringLiteral( "String" ) )
Expand Down
11 changes: 11 additions & 0 deletions tests/src/core/testqgsexpression.cpp
Expand Up @@ -1334,6 +1334,17 @@ class TestQgsExpression: public QObject
QTest::newRow( "try invalid without alternative" ) << "try(to_int('a'))" << false << QVariant();

// Datetime functions
QTest::newRow( "make date" ) << "make_date(2012,6,28)" << false << QVariant( QDate( 2012, 6, 28 ) );
QTest::newRow( "make date invalid" ) << "make_date('a',6,28)" << true << QVariant();
QTest::newRow( "make date invalid 2" ) << "make_date(2012,16,28)" << true << QVariant();
QTest::newRow( "make time" ) << "make_time(13,6,28)" << false << QVariant( QTime( 13, 6, 28 ) );
QTest::newRow( "make time with ms" ) << "make_time(13,6,28.5)" << false << QVariant( QTime( 13, 6, 28, 500 ) );
QTest::newRow( "make time invalid" ) << "make_time('a',6,28)" << true << QVariant();
QTest::newRow( "make time invalid 2" ) << "make_time(2012,16,28)" << true << QVariant();
QTest::newRow( "make datetime" ) << "make_datetime(2012,7,8,13,6,28)" << false << QVariant( QDateTime( QDate( 2012, 7, 8 ), QTime( 13, 6, 28 ) ) );
QTest::newRow( "make datetime with ms" ) << "make_datetime(2012,7,8,13,6,28.5)" << false << QVariant( QDateTime( QDate( 2012, 7, 8 ), QTime( 13, 6, 28, 500 ) ) );
QTest::newRow( "make datetime invalid" ) << "make_datetime(2012,7,8,'a',6,28)" << true << QVariant();
QTest::newRow( "make datetime invalid 2" ) << "make_datetime(2012,7,8,2012,16,28)" << true << QVariant();
QTest::newRow( "to date" ) << "todate('2012-06-28')" << false << QVariant( QDate( 2012, 6, 28 ) );
QTest::newRow( "to interval" ) << "tointerval('1 Year 1 Month 1 Week 1 Hour 1 Minute')" << false << QVariant::fromValue( QgsInterval( 34758060 ) );
QTest::newRow( "day with date" ) << "day('2012-06-28')" << false << QVariant( 28 );
Expand Down

0 comments on commit c2715d7

Please sign in to comment.