1
tedsmith
1/1/1970 Joined Date for Everyone
  • 2005/10/7 17:13

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


A few months back I imported about 200 users from a text file that was created from an export of users from an old web site. I modified Christians import script to import all the users into my new site which worked a treat.

Unfortunately they have all inherited a 'Joined Date' of 1/1/1970 01:00.

I appreciate they will all have inherited the same date but I was hoping it would have been the date I ran the script as opposed to 1/1/1970. Is there any way I can tell XOOPS "Change all joined dates of 1/1/1970 to 01/08/05", for example?

Thanks

Ted

2
tedsmith
Re: 1/1/1970 Joined Date for Everyone
  • 2005/10/7 18:28

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Using phpMyAdmin I notice there's a table called 'DBPrefix_users and within that a column called 'user_regdate' where all the 01/01/1970 dates are stored as '0'.

What would be the SQL to execute to simply change these values by saying "Where user_regdate = '0' change to 01/08/05" ?

Ted

3
TottoBG
Re: 1/1/1970 Joined Date for Everyone
  • 2005/10/7 19:03

  • TottoBG

  • Not too shy to talk

  • Posts: 111

  • Since: 2005/8/18


The value '0' in the DB exactly responds to the date 1/1/1970 cause it uses the timestamp format - that is the number of seconds from the beginning of the unix epoch (which starts at 1/1/1970). So having 0 in that field could be because of bad transfer of the info, maybe the script hasn't coppied the 'user_regdate' field and is set to default '0', so that's the final effect.

4
tedsmith
Re: 1/1/1970 Joined Date for Everyone
  • 2005/10/7 19:19

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Thanks for your reply, but I am already aware of why it's showing as 1970 (the date being the birthday of UNIX) and why the script did not actually specify a date to use so it's using the default value.

I need to know how to tell my database to say 'Hey, look for all the registration dates that are currently set as 0 and chnage them to 01/08/05".

Thanks

Ted

5
jdseymour
Re: 1/1/1970 Joined Date for Everyone

I believe you need to calculate the number of seconds since 1/1/1970 and enter that amount. Entering the date will not be understood I do not think.

6
tedsmith
Re: 1/1/1970 Joined Date for Everyone
  • 2005/10/7 19:37

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Yes, sorry, I did not explain myself properly. I realise that too, but what I need to know is the actual SQL script to run in phpMyAdmin so that it changes all the values of '0' in that column 'reg_date' to whatever the value of seconds is that have passed since 1.1.70. So lets say it's a million seconds, I need to create a SQL statement that says "for each user, where reg_date contains '0' replace with 1000000000"

Ted

7
TottoBG
Re: 1/1/1970 Joined Date for Everyone
  • 2005/10/7 19:56

  • TottoBG

  • Not too shy to talk

  • Posts: 111

  • Since: 2005/8/18


UPDATE DBPrefix_users SET user_regdate = unix_timestamp(now()) WHERE user_regdate = 0 - that will write them in the db as registered at the moment you execute it. or you could use a concrete date and time as executing

UPDATE DBPrefix_users SET user_regdate = unix_timestamp('yyyy-mm-dd hh:ii:ss') WHERE user_regdate = 0 where replacing the date format with a concrete date

Login

Who's Online

134 user(s) are online (59 user(s) are browsing Support Forums)


Members: 0


Guests: 134


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits