Order Firestore data by TimeStamp in Ascending order

Question

I am storing Ideas posted by the application in Firestore. The data is stored in Firestore like this Ideas/{documentID}/IdeaObject. The issue is when I retrieve the data it is not sorted w.r.t time it was posted. The ideas that are retrieved are in according to the id's of their documentID which is automatically create by Firestore. I have used ServerTimestamp in my Model Class and also when I retrieve it, I use the orderBy method with my Firestore reference but still nothing.

Idea.java

public class Idea {
    @ServerTimestamp
    private Date date;
    private String title, idea, timeCommitment, ideaStage, postedBy, website, videoPitch;
    private int views, favorites;
    private ArrayList<String> lookingFor = new ArrayList<>();
    private ArrayList<String> tags = new ArrayList<>();
    private String userID;
    private String timeStamp;

    public Idea() {
    }

    public Idea(String title, String idea, String timeCommitment, String ideaStage, String postedBy, String website, String videoPitch, int views, int favorites, ArrayList<String> lookingFor, ArrayList<String> tags, String userID, String timeStamp) {
        this.title = title;
        this.idea = idea;
        this.timeCommitment = timeCommitment;
        this.ideaStage = ideaStage;
        this.postedBy = postedBy;
        this.website = website;
        this.videoPitch = videoPitch;
        this.views = views;
        this.favorites = favorites;
        this.lookingFor = lookingFor;
        this.tags = tags;
        this.userID = userID;
        this.timeStamp = timeStamp;
    }

Ideas Posting Method

  private void postIdea() {

        final String ideaID = UUID.randomUUID().toString();
        final SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss");
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());


        Idea ideas = new Idea(title, idea, timeCommitment, ideaStage, AppValues.fullName, website, videoPitch, 0, 0, lookingFor, tags, AppValues.userId, "" + timestamp.getTime());

        firestoreDb.collection("ideas")
                .document(ideaID)
                .set(ideas)
                .addOnSuccessListener(new OnSuccessListener<Void>() {
                    @Override
                    public void onSuccess(Void aVoid) {
                        postIdeaUser(ideaID);
                    }
                })
                .addOnFailureListener(new OnFailureListener() {
                    @Override
                    public void onFailure(@NonNull Exception e) {
                        hideLoadingDialog();
                        showToast(e.getMessage());
                    }
                });
    }

Retrieving all Ideas by Time

   firestoreDb.collection("ideas")
                .orderBy("timeStamp", Query.Direction.ASCENDING)
                .get()
                .addOnCompleteListener(new OnCompleteListener<QuerySnapshot>() {
                    @Override
                    public void onComplete(@NonNull Task<QuerySnapshot> task) {
                        if (task.isSuccessful()) {

                            ideaArrayList = new ArrayList<>();
                            ideaArrayList.clear();

                            for (DocumentSnapshot documentSnapshot : task.getResult()) {
                                Idea idea = documentSnapshot.toObject(Idea.class);
                                if (!idea.getUserID().equals(AppValues.userId)) {
                                    ideaArrayList.add(idea);
                                }
                            }

                            callAdapter();

                        } else {
                            Log.d(TAG, "Error getting documents: ", task.getException());
                            progressBar.setVisibility(View.GONE);
                            swipeRefresh.setEnabled(true);
                            errorText.setVisibility(View.VISIBLE);
                        }
                    }
                });

What I want to achieve is retrieve all the ideas and have them ordered ascending by the TimeStamp value.

Why are you storing both a field called `date` annotated with `@ServerTimestamp` and a formatted `timeStamp` string? Seems like you would just want the `@ServerTimestamp` value, and make your queries use that. — Doug Stevenson, May 08 '18 at 02:25
@DougStevenson Can you please specify how can I use the serverTimestamp value in query? I tried using this when I retrieve the data.orderBy("date", Query.Direction.ASCENDING) but still nothing. — Ebad Ali, May 08 '18 at 02:30
What does "nothing" mean? Does `task.getException()` return something? — Alex Mamo, May 08 '18 at 07:24

Alex Mamo · Accepted Answer · 2021-11-20T15:04:33.900

54

You cannot use a String (timeStamp) when querying your database instead of a Date (date) and expect to behave as it was a date. So to solve this, please change the following line of code:

firestoreDb.collection("ideas")
            .orderBy("timeStamp", Query.Direction.ASCENDING)

to

firestoreDb.collection("ideas")
            .orderBy("date", Query.Direction.ASCENDING)

To make it work, this kind of query requires an index. To create one, please check my answer from the following post:

Firestore whereEqualTo, orderBy and limit(1) not working

edited Nov 20 '21 at 15:04

answered May 08 '18 at 07:24

Alex Mamo

130,605
17
163
193

1

I am using long type timestamp still not getting desired result. – Ankit Mishra Nov 19 '21 at 08:59
@AnkitMishra You need to use a [Firestore Timestamp](https://stackoverflow.com/questions/48474957/servertimestamp-is-allways-null-on-firebase-firestore/48475027), not a long value. – Alex Mamo Nov 19 '21 at 09:03
Well, I am using Epoch Timestamp of type number. So think it like a number and I want to sort according to that number. if number is bigger it should come first. – Ankit Mishra Nov 19 '21 at 09:07
@AnkitMishra Without seeing your code and database schema, I cannot be much of a help. So please post a new question, here on StackOverflow, using its own [MCVE](https://stackoverflow.com/help/mcve), so I and other Firebase developers can help you. – Alex Mamo Nov 19 '21 at 09:35
can you please check twitter DM? – Ankit Mishra Nov 19 '21 at 09:59
1

I just created index and its working perfectly fine with timestamp as I wanted.. Thanks for the help. – Ankit Mishra Nov 20 '21 at 12:03
I would love. But it would be awesome if you give a reference to my answer for this post.. I have wrote answer too.. here it is https://stackoverflow.com/a/70045716/13272795 . It helped me a lot I want other find it helpful too. So please consider putting my answer link to the your answer. and yes I already did up voted. – Ankit Mishra Nov 20 '21 at 14:02
That index issue has already an [answer](https://stackoverflow.com/questions/50305328/firestore-whereequalto-orderby-and-limit1-not-working). – Alex Mamo Nov 20 '21 at 15:01
It would be helpful if you add mine answer or the answer you told in comment in your accepted answer. I think it would helpful a lot. – Ankit Mishra Nov 20 '21 at 15:02
1

Btw I must say you rocks in firebase thing. – Ankit Mishra Nov 20 '21 at 15:04
@AnkitMishra Yes, you're right. I just edited my answer. – Alex Mamo Nov 20 '21 at 15:04
Btw can you suggest me something on smart searching algorithm. I want to search something like I do in mysql. But unfortunately firebase has very limited searching capabilities. – Ankit Mishra Nov 20 '21 at 15:06
1

@AnkitMishra Maybe this [approach](https://firebase.google.com/docs/firestore/query-data/queries#in_not-in_and_array-contains-any) would help. – Alex Mamo Nov 20 '21 at 16:13

score 16 · Answer 2 · answered Sep 30 '19 at 13:27

In my case, the vanilla version would be,

firestoreDb.collection("ideas")
     .orderBy("timestamp", "asc")

firestoreDb.collection("ideas")
     .orderBy("timestamp", "desc")

"ideas" is the name of your collection

"timestamp" is the key or field or column name to be used for sorting.

"asc" or "desc" is the option to be used for the order

score 1 · Answer 3 · answered Aug 12 '20 at 04:15

1

You have to also add index in your firebase account which is in database -> Indexes -> add index

answered Aug 12 '20 at 04:15

Jeevan Rupacha

3,055
1
15
29

You mean not in the code? – Adan Vivero Dec 13 '21 at 20:29

score 1 · Answer 4 · edited Jul 25 '22 at 14:54

1

you just have to change the data type of your timestamp to Timestamp

--- import com.google.firebase.Timestamp

(in kotlin)

val timeStamp : Timestamp = Timestamp.now()

edited Jul 25 '22 at 14:54

Luca Ziegler

3,236
1
22
39

answered Jul 24 '22 at 05:28

Himanshu Yadav Anshu

140
1
8

score 0 · Answer 5 · answered Nov 20 '21 at 12:00

0

Just create index and it will start working with timestamp too. Just make sure timestamp is a number type and not a string.

To create index best way is to search FAILED_PRECONDITION: in Android Studio logcat and you will see a link just follow that link and create index.
Example : FAILED_PRECONDITION: The query requires an index. You can create it here: ...

Feel free to ask any query.

answered Nov 20 '21 at 12:00

Ankit Mishra

530
8
16

What do you mean to create an index? Also, how do you implement that index? – Adan Vivero Dec 13 '21 at 20:14
I mean database index. – Ankit Mishra Dec 15 '21 at 07:17
Is that done in the kotlin code? – Adan Vivero Dec 15 '21 at 20:27

score 0 · Answer 6 · answered Dec 13 '21 at 20:48

You can order your firebase data on the console, following these steps.

Step 1: Click on those three lines on your documents collection.

Step 2: From there, you get the field that you want to filter by. You enter whichever field that is, in my case it is "timestamp".

Step 3: Select the Sort Results you want to apply, for my case it is "asc", and then you click apply, and it should order what you want in the database.

Order Firestore data by TimeStamp in Ascending order

6 Answers6

Linked