SQL Query timestamp date field and group all results by date

Question

I'm trying to create a list and i'm being stumped. So I have a table that with a start_date field that holds Unix Timestamps for the date. I'm trying to query the table and extract the date from the timestamp, then any result with that date will be under that day and so on. Almost like a calendar with events on it in list view.

I'm doing this with PostgreSQL and PHP. Not sure how to write that query. I see functions like extract, date_part etc for PostgreSQL however, still unsure how to get the correct query(s).

Any help or examples would be appreciated.

Why are you using UNIX timestamps when pgsql has a perfectly good timestamp data type? — Jonnix, Oct 03 '12 at 18:40
was so close to posting FROM_UNIXTIME() until I saw that tags ;/ — wesside, Oct 03 '12 at 18:48

Arjun Abhynav · Answer 1 · 2012-10-03T19:11:42.257

1

date('d M Y', $timestamp); should give you the date, month and year of that particular UNIX timestamp.

If you would like to have it in a list view, run a select query on the database order by start_date field (ascending or descending), and fetch it in $row. Then inside the loop where you process the record one by one, do this:

$prevDate = ' ';
$currentDate = ' ';

for each ( $row ) //however it goes
{
    $currentDate = date('d M Y',$row['start_date']);
    if($prevDate != $currentDate)
    {
        echo "Day : ".$currentDate;
    }
    echo $row['other-fields'];
    $prevDate = $currentDate;
 }

However, PostgreSQL supports date datatype, and using that should simplify things at your end.

http://www.postgresql.org/docs/8.2/static/datatype-datetime.html

edited Oct 03 '12 at 19:11

answered Oct 03 '12 at 19:00

Arjun Abhynav

543
4
15

Thanks but wouldn't they all have to be identical for order by to be effective. That's why I was trying to get the actual date because since it's in a timestamp it would have be down to the millisecond. None of them will ever be exact time, but they could be on the same day. That's why I want extract the date and list(group) by date and then everything that posted for that day. – Panama Jack Oct 03 '12 at 20:40
1

They don't require to be identical. This would also work, since I'm comparing $currentDate and $prevDate which hold just the dates (that have been converted from unix timestamp using `date('d M Y',$timestamp)`). So, the difference in seconds or hours would not affect the execution of the code. And also the `date( )` PHP function offers you a better flexibility as to how you want the UNIX timestamp to be displayed too. http://php.net/manual/en/function.date.php – Arjun Abhynav Oct 04 '12 at 13:09

score 0 · Accepted Answer · edited May 23 '17 at 11:43

0

Simplest / fastest way to extract a date from a Unix epoch:

SELECT to_timestamp(my_epoch_ts)::date;

Example:

SELECT to_timestamp(1349118398)::date

Result:

2012-10-01

That's making use of the to_timestamp() function, followed by a cast to date: ::date.

More details and links at the end of this recent related answer.

edited May 23 '17 at 11:43

Community

1
1

answered Oct 03 '12 at 19:11

Erwin Brandstetter

605,456
145
1,078
1,228

You both actually gave me the needed parts to do what I needed. You helped with the query and Arjun gave an example of writing it. I can only accept one and since I did say help with the query, I'll accept your answer. – Panama Jack Oct 04 '12 at 03:29
@Pjack: You can always upvote, so Arjun gets some gratification, too. – Erwin Brandstetter Oct 04 '12 at 13:54

SQL Query timestamp date field and group all results by date

2 Answers2