How to date format an SQL result using Diesel?

Question

I am using Diesel to query a DB in PostgreSQL using JOINs:

let product_id = 1;
sales::table
     .inner_join(product::table)
     .select((
         product::description,
         sales::amount,
         sales::date_sale
     ))
     .filter(sales::product_id.eq(product_id))
     .load(&diesel::PgConnection)

My model:

pub struct Sales {
    pub id: i32,
    pub product_id: Option<i32>,
    pub amount: Option<BigDecimal>,
    pub date_sale: Option<NaiveDateTime>
}

The result is as expected, but I need to give a date format to the field sales::date_sale that in pgadmin I do it with to_char(date_sale, 'dd/mm/YYYY').

Is it possible to use to_char in Diesel or in what way can I modify the data that the Diesel ORM brings me?

Answer 1

In addition to the answers provided by harmic there are two more possibilities to solve this problem.

sql_function!

Diesel provides an interface to easily define query ast nodes for sql functions that are not provided by diesel itself. Users are encouraged to use this functionality to define missing functions on their own. (In fact diesel uses internally the same method to define query ast nodes for sql functions provided out of the box). The defined query ast node is usable in every context where the types of its expression are valid, so it can be used in select and where clauses. (This is basically the type safe version of the raw sql solution above)

For the given question something like this should work:

#[derive(Queryable)]
pub struct Sales {
    pub id: i32,
    pub product_id: Option<i32>,
    pub amount: Option<BigDecimal>,
    pub date_sale: Option<String>,
}

sql_function! {
    fn to_char(Nullable<Timestamp>, Text) -> Nullable<Text>;
}

let product_id = 1;
sales::table
     .inner_join(product::table)
     .select((
         product::description,
         sales::amount,
         to_char(sales::date_sale, "dd/mm/YYYY")
     ))
     .filter(sales::product_id.eq(product_id))
     .load(&diesel::PgConnection);

#[derive(Queryable)] + #[diesel(deserialize_as = "Type")]

Diesels Queryable derive provides a way to apply certain type manipulations at load time via a custom attribute. In combination with the chrono solution provided by harmic this gives the following variant:

#[derive(Queryable)]
pub struct Sales {
    pub id: i32,
    pub product_id: Option<i32>,
    pub amount: Option<BigDecimal>,
    #[diesel(deserialize_as = "MyChronoTypeLoader")]
    pub date_sale: Option<String>
}

struct MyChronoTypeLoader(Option<String>);

impl Into<Option<String>> for MyChronoTypeLoader {
    fn into(self) -> String {
        self.0
    }
}

impl<DB, ST> Queryable<ST, DB> for MyChronoTypeLoader
where
    DB: Backend,
    Option<NaiveDateTime>: Queryable<ST, DB>,
{
    type Row = <Option<NaiveDateTime> as Queryable<ST, DB>>::Row;

    fn build(row: Self::Row) -> Self {
        MyChronoTypeLoader(Option::<NaiveDateTime>::build(row).map(|d| d.format("%d/%m/%Y").to_string()))
    }
}