How to select just date from timestamp in SQLite3 and PostgreSQL

Question

In my Rails project, I have a scores table full of timestamp rows that I need to query and find the LATEST record from each day. My production database is PostgreSQL though I develop/test locally in sqlite3.

I'm having trouble structuring the SQL query to pull just the date from the timestamp. So if this is the timestamp: 2010-12-13 23:03:40.485012+0000, then I need to just examine 2010-12-13 and ignore the time.

Scores table:

id          :integer  
name        :string  
score       :integer  
updated_at  :timestamp

And since I have multiple rows per day for each name, I'd like to find the MAX timestamp if each day, and just look at the latest score of each day for each name.

I've tried using the DATE() function without any luck. Anyone know the best method of doing this?

dan04 · Accepted Answer · 2010-12-22 08:03:32Z

1

In the the SQLite date functions, you need a colon to separate hours and minutes in the timezone.

sqlite> select date('2010-12-13 23:03:40.485012+0000');
[NULL]
sqlite> select date('2010-12-13 23:03:40.485012+00:00');
2010-12-13

My production database is PostgreSQL though I develop/test locally in sqlite3.

This is a bad idea. Test with the same database you use for production.

answered Dec 22, 2010 at 8:03

dan04

92k23 gold badges169 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2010-12-15 18:53:05Z

0

For PostgreSQL you can use:

CAST(updated_at AS DATE)

answered Dec 15, 2010 at 18:53

user330315

2 Comments

Scott Allen Over a year ago

Thanks, trying to get something locally that will work with SQLite3 first. Not sure why "SELECT DATE(updated_at) from Scores" isn't working.

user330315 Over a year ago

According to the SQLLite manual DATE() converts a string to a DATE, it does not extract the date from a timestamp. A bit off-topic but I would never recommend to test an application with a different DBMS than what is used in production.

Adrian Toman · Accepted Answer · 2012-07-18 01:20:49Z

0

In PostgreSQL you can use ::.

declare _now timestamp with time zont := now();
declare dt date := _now::date;

edited Jul 18, 2012 at 1:20

Adrian Toman

11.5k6 gold badges50 silver badges63 bronze badges

answered Dec 22, 2010 at 8:25

Bonshington

4,0622 gold badges29 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to select just date from timestamp in SQLite3 and PostgreSQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related