How to parse Datetime in a specific format?

[marvin-hansen]

Alright,

this one bugs me left and right. I have a table in timescaleDB, which is build atop of pg sql and pg SQL compliant. Essentially, when reading timestamps from PG, I only get the default Jan/01/01 on all timestamps, which is clearly wrong. I searched the internet and found a couple of related issues although I am not exactly sure what's the exact cause here.

My question is: How do I pass the custom date format to go-pg to restore my timestamps when querying my data?

Here is my table DDL:

create table pricedata
(
    date   timestamp,
    symbol text NOT NULL,
    open   double precision,
    high   double precision,
    low    double precision,
    close  double precision,
    fec    text
);

When loading data, I have to import all timestamps into the temporary "fec" column and then convert into the target date format. I found this workaround on multiple pg forums and it works. In my case, I did this:

update pricedata set date = to_timestamp(fec, 'MM/DD/YYYY HH24:MI');

alter TABLE pricedata drop COLUMN fec;

When querying the data with SQL, results are fine:

2021-05-16 00:30:00.000000, 3790, 3806.13, 3761.39, 3766.15

When running the exact same query through go-pg, all timestamps are set to default

	db := dbm.DBComponent.DB()
	query := getResampleQuery(samplingRate, interval, tableName)
	result := &[]model.DBBar{}
	_, queryErr := db.Query(result, query)
	if queryErr != nil {
		log.Println(queryErr)
		return result, queryErr
	}
	return result, nil

The underlying struct looks like so:

type DBBar struct {
	Date       time.Time `pg:",pk,unique,type:timestamp"` // PK for internal DB use
	Day        time.Time `pg:",type:timestamp"`
	Symbol     string
	Open       float64
	High       float64
	Low        float64
	Close      float64
}

The Day field must be present because the SQL query I use creates a temp. column called 'day' for resampling data resolution to a target value but go-pg would fail to insert the data into the struct if the day field would be missing. Just as clarification of the apparent field redundancy in the struct. Also, the primary key over the timestamp stems from the fact that all bar data are stored in a hyper table specialized for timeseries.

And here is the query output, essentially all bars come with the same Jan/01/01 timestamp, which is useless.

 Date: 0001-01-01 00:00:00 +0 000 UTC , Open: 1756.780000, High: 1757.960000, Low: 1755.

However, the correct format in the DB and the raw data should be:

2021-05-16 00:34 

For some reasons, PG adds a ton of trailing zeros, which seems to relate to the timestamp format, so the actual timestamp in the DB looks like so:

2021-05-16 00:34:00.000000

Essentially, it looks like when passing the empty array of DBBars into go-pg, go cannot determine the custom format of the timestamp and then sets all dates to default.

How do I pass the custom date format to go-pg to restore my timestamps?

I know I can parse strings and convert them into datetime, but I am getting about a quarter million bars for each query thus converting datetime already during query time is much preferred, obviously.

[marvin-hansen]

Solved!

This code example was invaluable:

https://github.com/go-pg/pg/blob/v10/example_custom_test.go