Parsing text field as datetime

[syrm]

Hello,

I have this query :

SELECT json_agg(a.*) as answers
FROM poll p
INNER JOIN poll_answer pa ON (p.id = pa.poll_id)
INNER JOIN answer a ON (a.id = pa.answer_id)
WHERE p.id = '018b682b-aed3-7153-ac20-7eb447693778'
GROUP BY p.id

The result look like this

[{"name":"Thé","created_at":"2023-10-25 18:50:08.081643+00:00"}, 
 {"name":"Café","created_at":"2023-10-25 18:50:08.081643+00:00"}]

I want to be able to parse the created_at field as a datetime

With this struct

type Answer struct {
  Name string
  CreatedAt time.Time
}

It would be convenient for Scany to understand that the date in text field is a date.

[georgysavva]

Hi @syrm. Scany doesn't make any data transformation or data type handling. Its sole purpose is to direct the data coming from the database to the right struct fields. The transformation from database types to Go types happens in the underlying database library, for example, pgx.

If you have a query like this:

SELECT '2023-10-25 18:50:08.081643+00:00' as "created_at", 'Café' as "name";

And you call scany like this:

type Answer struct {
  Name string
  CreatedAt time.Time
}
answers:= []Answer{}
pgxscan.Select(ctx, query, &answers)

It should work with scany as long as the underlying database library can parse the date string into the Go's time.Time type.

Feel free to reopen the issue if you have any more questions.