Change Datetime value

My apologies for the length of this post but I'm hoping that some
background will help clarify my request. I'm not using MySQL for
anything other then the database for my guestbook. I had a previous
guestbook that became corrupted and my new guestbook uses MySQL. I have
access to the database via phpMyAdmin and I can display the entry data
fields.

What I am doing is taking the entries from my previous guestbook and
entering them into the new guestbook. The problem is that the date and
time entry is the current time and date and I want it to be the time
and date of the original entry. When I view the datetime field in
phpMyAdmin the value is listed as a numerical value that, at least to
me, does not correlate to a month, day, year and time value. Example: A
10/20/2006 11:25 pm entry shows up as 1161408341 in the date>value
field in phpMyAdmin.

Is there a correlation table or some sort of formula that I can use to
alter the entered date>value number to the date and time that I want?

MySQL version is 4.1 and phpMyAdmin version is 2.6.4

Thanks for any assistance.

Re: Change Datetime value

Desert Rider wrote:
> My apologies for the length of this post but I'm hoping that some
> background will help clarify my request. I'm not using MySQL for
> anything other then the database for my guestbook. I had a previous
> guestbook that became corrupted and my new guestbook uses MySQL. I have
> access to the database via phpMyAdmin and I can display the entry data
> fields.
>
> What I am doing is taking the entries from my previous guestbook and
> entering them into the new guestbook. The problem is that the date and
> time entry is the current time and date and I want it to be the time
> and date of the original entry. When I view the datetime field in
> phpMyAdmin the value is listed as a numerical value that, at least to
> me, does not correlate to a month, day, year and time value. Example: A
> 10/20/2006 11:25 pm entry shows up as 1161408341 in the date>value
> field in phpMyAdmin.
>
> Is there a correlation table or some sort of formula that I can use to
> alter the entered date>value number to the date and time that I want?

It's seconds since 00:00 January 1, 1970.

00:01 January 1, 1970 = 1
00:00 January 2, 1970 = 86400
and so on to 2038 when the signed 32 bit value will have reached it's largest
value (as there are a few years until then, we will see 64 bit to be used for
unix time, but sadly this will lead to trouble with some closed sourced
programs in the same manner as all the old ms-dos/ms-windows programs got at 2k).


//Aho

Re: Change Datetime value

Thanks for the reply. Sounds like I'll get a chance to utilize my
limited computational skills. Unless...is there a reference guide or
table anywhere that lists yearly totals since 1970? That would get me
close and then I could compute the additional seconds for a given
month, day and time.

J.O. Aho wrote:
> Desert Rider wrote:
> > My apologies for the length of this post but I'm hoping that some
> > background will help clarify my request. I'm not using MySQL for
> > anything other then the database for my guestbook. I had a previous
> > guestbook that became corrupted and my new guestbook uses MySQL. I have
> > access to the database via phpMyAdmin and I can display the entry data
> > fields.
> >
> > What I am doing is taking the entries from my previous guestbook and
> > entering them into the new guestbook. The problem is that the date and
> > time entry is the current time and date and I want it to be the time
> > and date of the original entry. When I view the datetime field in
> > phpMyAdmin the value is listed as a numerical value that, at least to
> > me, does not correlate to a month, day, year and time value. Example: A
> > 10/20/2006 11:25 pm entry shows up as 1161408341 in the date>value
> > field in phpMyAdmin.
> >
> > Is there a correlation table or some sort of formula that I can use to
> > alter the entered date>value number to the date and time that I want?
>
> It's seconds since 00:00 January 1, 1970.
>
> 00:01 January 1, 1970 = 1
> 00:00 January 2, 1970 = 86400
> and so on to 2038 when the signed 32 bit value will have reached it's largest
> value (as there are a few years until then, we will see 64 bit to be used for
> unix time, but sadly this will lead to trouble with some closed sourced
> programs in the same manner as all the old ms-dos/ms-windows programs got at 2k).
>
>
> //Aho

Re: Change Datetime value

> Thanks for the reply. Sounds like I'll get a chance to utilize my
> limited computational skills. Unless...is there a reference guide or
> table anywhere that lists yearly totals since 1970? That would get me
> close and then I could compute the additional seconds for a given
> month, day and time.

you can easily convert the timestamp to the date using something like:-

SELECT FROM_UNIXTIME( 1161408341 )

you can use that function in a select query when retrieving data as well.

Re: Change Datetime value

Unfortunately my data from the previous entries does not have
timestamps available, only month, day, year and TOD (24 hr). I really
need something that allows me to enter a date and time e.g. 4/21/2003
23:25 and then returns the proper timestamp in seconds since
01/01/1970. I could then enter that in the date>value field and it
would put that date/time on the entry. I've done some googleing and
have found various references to programming that will do that but way
beyond my expertise. Even if I could find something that would
accurately give me the amount of days say between 01/01/1970 and say
4/21/2003 I could then compute the seconds and then add the additional
time for that day. Alas, I'm numeracy challanged and so require
something pretty simple.

peter wrote:
> > Thanks for the reply. Sounds like I'll get a chance to utilize my
> > limited computational skills. Unless...is there a reference guide or
> > table anywhere that lists yearly totals since 1970? That would get me
> > close and then I could compute the additional seconds for a given
> > month, day and time.
>
> you can easily convert the timestamp to the date using something like:-
>
> SELECT FROM_UNIXTIME( 1161408341 )
>
> you can use that function in a select query when retrieving data as well.

Re: Change Datetime value

Desert Rider wrote:
> Unfortunately my data from the previous entries does not have
> timestamps available, only month, day, year and TOD (24 hr). I really
> need something that allows me to enter a date and time e.g. 4/21/2003
> 23:25 and then returns the proper timestamp in seconds since
> 01/01/1970. I could then enter that in the date>value field and it
> would put that date/time on the entry. I've done some googleing and
> have found various references to programming that will do that but way
> beyond my expertise. Even if I could find something that would
> accurately give me the amount of days say between 01/01/1970 and say
> 4/21/2003 I could then compute the seconds and then add the additional
> time for that day. Alas, I'm numeracy challanged and so require
> something pretty simple.

You can use DATEDIFF() to get differences between two dates/times or you can
directly use UNIX_TIMESTAMP().

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html

//Aho