Improve timezone handling

3 files changed, 16 insertions, 13 deletions

diff --git a/storage/entry.go b/storage/entry.go
index 7f3eaf4..176dcde 100644
--- a/storage/entry.go
+++ b/storage/entry.go
@@ -16,9 +16,9 @@ import (
 	"github.com/lib/pq"
 )
 
-// GetEntryQueryBuilder returns a new EntryQueryBuilder
-func (s *Storage) GetEntryQueryBuilder(userID int64, timezone string) *EntryQueryBuilder {
-	return NewEntryQueryBuilder(s, userID, timezone)
+// NewEntryQueryBuilder returns a new EntryQueryBuilder
+func (s *Storage) NewEntryQueryBuilder(userID int64) *EntryQueryBuilder {
+	return NewEntryQueryBuilder(s, userID)
 }
 
 // createEntry add a new entry.
diff --git a/storage/entry_query_builder.go b/storage/entry_query_builder.go
index 040c717..99fa4e5 100644
--- a/storage/entry_query_builder.go
+++ b/storage/entry_query_builder.go
@@ -20,7 +20,6 @@ type EntryQueryBuilder struct {
 	store              *Storage
 	feedID             int64
 	userID             int64
-	timezone           string
 	categoryID         int64
 	status             string
 	notStatus          string
@@ -157,7 +156,7 @@ func (e *EntryQueryBuilder) GetEntries() (model.Entries, error) {
 
 	query := `
 		SELECT
-		e.id, e.user_id, e.feed_id, e.hash, e.published_at at time zone '%s', e.title,
+		e.id, e.user_id, e.feed_id, e.hash, e.published_at at time zone u.timezone, e.title,
 		e.url, e.author, e.content, e.status, e.starred,
 		f.title as feed_title, f.feed_url, f.site_url, f.checked_at,
 		f.category_id, c.title as category_title, f.scraper_rules, f.rewrite_rules, f.crawler,
@@ -166,11 +165,12 @@ func (e *EntryQueryBuilder) GetEntries() (model.Entries, error) {
 		LEFT JOIN feeds f ON f.id=e.feed_id
 		LEFT JOIN categories c ON c.id=f.category_id
 		LEFT JOIN feed_icons fi ON fi.feed_id=f.id
+		LEFT JOIN users u ON u.id=e.user_id
 		WHERE %s %s
 	`
 
 	args, conditions := e.buildCondition()
-	query = fmt.Sprintf(query, e.timezone, conditions, e.buildSorting())
+	query = fmt.Sprintf(query, conditions, e.buildSorting())
 	// log.Println(query)
 
 	rows, err := e.store.db.Query(query, args...)
@@ -342,11 +342,10 @@ func (e *EntryQueryBuilder) buildSorting() string {
 }
 
 // NewEntryQueryBuilder returns a new EntryQueryBuilder.
-func NewEntryQueryBuilder(store *Storage, userID int64, timezone string) *EntryQueryBuilder {
+func NewEntryQueryBuilder(store *Storage, userID int64) *EntryQueryBuilder {
 	return &EntryQueryBuilder{
-		store:    store,
-		userID:   userID,
-		timezone: timezone,
-		starred:  false,
+		store:   store,
+		userID:  userID,
+		starred: false,
 	}
 }
diff --git a/storage/feed.go b/storage/feed.go
index 84d3a62..ae6d50a 100644
--- a/storage/feed.go
+++ b/storage/feed.go
@@ -52,13 +52,15 @@ func (s *Storage) Feeds(userID int64) (model.Feeds, error) {
 	feeds := make(model.Feeds, 0)
 	query := `SELECT
 		f.id, f.feed_url, f.site_url, f.title, f.etag_header, f.last_modified_header,
-		f.user_id, f.checked_at, f.parsing_error_count, f.parsing_error_msg,
+		f.user_id, f.checked_at at time zone u.timezone,
+		f.parsing_error_count, f.parsing_error_msg,
 		f.scraper_rules, f.rewrite_rules, f.crawler,
 		f.category_id, c.title as category_title,
 		fi.icon_id
 		FROM feeds f
 		LEFT JOIN categories c ON c.id=f.category_id
 		LEFT JOIN feed_icons fi ON fi.feed_id=f.id
+		LEFT JOIN users u ON u.id=f.user_id
 		WHERE f.user_id=$1
 		ORDER BY f.parsing_error_count DESC, f.title ASC`
 
@@ -117,13 +119,15 @@ func (s *Storage) FeedByID(userID, feedID int64) (*model.Feed, error) {
 	query := `
 		SELECT
 		f.id, f.feed_url, f.site_url, f.title, f.etag_header, f.last_modified_header,
-		f.user_id, f.checked_at, f.parsing_error_count, f.parsing_error_msg,
+		f.user_id, f.checked_at at time zone u.timezone,
+		f.parsing_error_count, f.parsing_error_msg,
 		f.scraper_rules, f.rewrite_rules, f.crawler,
 		f.category_id, c.title as category_title,
 		fi.icon_id
 		FROM feeds f
 		LEFT JOIN categories c ON c.id=f.category_id
 		LEFT JOIN feed_icons fi ON fi.feed_id=f.id
+		LEFT JOIN users u ON u.id=f.user_id
 		WHERE f.user_id=$1 AND f.id=$2`
 
 	err := s.db.QueryRow(query, userID, feedID).Scan(