We would like to query for users whose last login was within 1 year from current date. I found this query from docs: SELECT u.username AS username , psname.propertyvalue AS FullName , psemail.propertyvalue AS email, pslogincount.propertyvalue AS logincount, to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin FROM userbase u JOIN ( SELECT id, entity_id FROM propertyentry WHERE property_key = 'fullName' ) entityName ON u.id = entityName.entity_id JOIN ( SELECT id, entity_id FROM propertyentry WHERE property_key = 'email' ) entityEmail ON u.id = entityEmail.entity_id left JOIN ( SELECT id, entity_id FROM propertyentry WHERE property_key = 'login.count' ) loginCount ON u.id = loginCount.entity_id left JOIN ( SELECT id, entity_id FROM propertyentry WHERE property_key = 'login.previousLoginMillis' ) lastLogin ON u.id = lastLogin.entity_id JOIN propertystring psname ON entityName.id=psname.id JOIN propertystring psemail ON entityEmail.id = psemail.id left JOIN propertystring pslogincount ON loginCount.id=pslogincount.id left JOIN propertystring pslastlogin ON lastLogin.id = pslastlogin.id ORDER BY username; This seems to be what we need but the line "to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin" is not accepted in sql server. Does anyone knows whae query we can use to replace this line? Thanks in advance

I found the solution with the help of Atlassian support. The equivalent in SQL of: to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin Is dateadd(ss, cast( cast(pslastlogin.propertyvalue as varchar(1000)) as numeric) / 1000,'01/01/1970') as lastlogin

Community
Q&A
Jira
Questions
DB script for searching active users

Technical Error Is New People Cannot Log Into Jira Core (Free Version)

Hi Jira Core Support,

I am the Administrator for Jira Core.

We invite our 1st new team member by sending out invite to their email(Gmail).

They registered as new user via email.

Then when they try login into our Jira Core(Free) URL, there was a technical error, and could not log in, the message ask us to talk to Support.

We exit out of Chrome, and attempt to login for a few times, basically could not login.

Please assist.

Thanks.

2 answers

1 accepted

0 votes

Answer accepted

I found the solution with the help of Atlassian support.

The equivalent in SQL of:

to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin

dateadd(ss, cast( cast(pslastlogin.propertyvalue as varchar(1000)) as numeric) / 1000,'01/01/1970') as lastlogin

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

0 votes

http://beyondrelational.com/modules/2/blogs/70/posts/10957/sql-servers-equivalent-of-oracle-functions-part-ii.aspx

Says to use CONVERT

http://www.w3schools.com/sql/func_convert.asp

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

I got: 'Arithmetic overflow error converting expression to data type datetime.'

Looks like its not what I need.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Forums

Q&A

Community resources

Support

Top groups

Community resources

Support

Learn

Community resources

Support

Events

Community resources

Support

Technical Error Is New People Cannot Log Into Jira Core (Free Version)

2 answers

1 accepted

Suggest an answer

Was this helpful?

Thanks!

TAGS

Community showcase

Atlassian Community Events