EPrints Technical Mailing List Archive

See the EPrints wiki for instructions on how to join this mailing list and related information.

Message: #10093


< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.


CAUTION: This e-mail originated outside the University of Southampton.
I was able to construct an SQL query that does the job, it's this:

SELECT COUNT(*)
FROM user
WHERE orcid IS NOT NULL
  AND orcid != ''
  AND orcid_token_expires_year IS NOT NULL
  AND orcid_token_expires_year <= 2044;

Then I spent the rest of the day trying to extract this same info using the EPrints API / Perl script, and I just couldn't get it to work!   One error after another after another...  defeat after defeat.  AI coding bots had equally little success.  I think the info is in a field in the user object called orcid_token_expires, but how to extract the info to get that same information proved too much.  I will try again, just because now I have to get it, but if anyone can help, it would be greatly appreciated!

Tomasz



From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Alan.Stiles [He/Him/They] <alan.stiles@open.ac.uk>
Sent: Monday, April 28, 2025 12:27 PM
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.
 

Attention This email originates from outside the concordia.ca domain. // Ce courriel provient de l'extérieur du domaine de concordia.ca




CAUTION: This e-mail originated outside the University of Southampton.
CAUTION: This e-mail originated outside the University of Southampton.

Sorry Tomasz,

To my understanding, the token is valid for 20 years, so it should be before 1/1/2045, (epoch time 2366841600)  but my fingers apparently couldn’t type the right date in the email last week…

 

Alan.

 

From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Tomasz Neugebauer <Tomasz.Neugebauer@concordia.ca>
Date: Monday, 28 April 2025 at 16:01
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

External email: if the sender or content looks suspicious, please click the Report Message icon, or forward it to report-phishing

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Thank you Alan!  That's clever, but I'm not if I understand.  The reason why it's before 01/01/2046 is because of the default length of time that the ORCID token is valid for? How do I go from "up to the end of 2024" to 01/01/2046 ?  

I suppose there is no date recorded in EPrints when the ORCID was synched/added to the repository?

 

Tomasz

 

 

 

 


From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Alan.Stiles [He/Him/They] <alan.stiles@open.ac.uk>
Sent: Friday, April 25, 2025 3:59 AM
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

 

Attention This email originates from outside the concordia.ca domain. // Ce courriel provient de l'extérieur du domaine de concordia.ca

 

 

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Possibly extract from the user table by token expiry date before 1/1/46 ? (epoch 2366841600)

 

Alan

 

From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Tomasz Neugebauer <Tomasz.Neugebauer@concordia.ca>
Date: Thursday, 24 April 2025 at 17:53
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

External email: if the sender or content looks suspicious, please click the Report Message icon, or forward it to report-phishing

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Hi everyone!  

 

I need to extract how many users have synched/added an ORCID in our repository, which the ORCID plugin already provides, but I need to have that limited by date.  So, I need to report how many ORCIDs up until the end of 2024.  Next year, I will need up until 2025, etc.  Any suggestions on how I can get that data out of our repo? 

 

Tomasz