Re: Changing a field's data in every record

[Hal.sz S.ndor]

2017/02/18 ... debt:

Is there a formula to change the format of the data in a single field in 
every record of a table?  She has a "timestamp” in a text field formatted as 
2017|02|16|04|58|42 and she wants to convert it to a more human readable format like 
 2017-02-16 @ 04:58:42


It now occurs to me, long after a working answer has been found, that 
because MySQL is none too picky about the strings that it turns into 
timestamps, that adding a DATETIME field, TS say, and thus updating is 
enough programming:


UPDATE tbl SET TS=timestampFormatted_CharacterStringfield;

MySQL takes any punctuation for a separator, and cares only about the 
numerals between the separators.


There is also the function STR_TO_DATE which takes a string to convert 
and a format string.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Changing a field's data in every record

[Reindl Harald]

Am 20.02.2017 um 10:35 schrieb Lucio Chiappetti:

On Sat, 18 Feb 2017, debt wrote:


How does one "grab" the existing data and then change it? Can this
be done solely in MySQL


I am not sure to understand your question ... you usually manipulate
data inside mysql ... but here it seems to me you are not talking of
changing the data VALUE (UPDATE table SET column=new value WHERE ...)
but of changing the DATA TYPE (ALTER TABLE table CHANGE or ALTER TABLE
table MODIFY depending on whether ypou change the column name too) or
even of (just) changing the FORMAT in which a value is displayed ...


but what he needs is a "select field from bla; foreach($data as 
$row){str_replace('x', 'y'); update table" for every record and he would 
like to do it in pure sql



Is there a formula to change the format of the data in a single
field in every record of a table?  She has a "timestamp??? in a
text field formatted as 2017|02|16|04|58|42 and she wants to
convert it to a more human readable format like 2017-02-16 @
04:58:42


curious ... the default format for a time stamp is very similar to the
latter (except for you funny at-sign) :

mysql> select time from north33 limit 1;
+-+
| time|
+-+
| 2013-01-22 12:47:47 |
+-+


and what has this to do with a TEXTFILED CONTAINING STRINGS LIKE 
"2017|02|16|04|58|42" - you need to understand what you quote first 
before you can comment it...



and only if I cast it to an integer I get the other form

mysql> select time+0 from north33 limit 1;
++
| time+0 |
++
| 20130122124747 |
++


completly unreleated to the problem


Anyhow I advise you to read the mysql manual and in particular the
function chapter and the "time and date function" subchapter. E.g. here

https://dev.mysql.com/doc/refman/5.7/en/functions.html


problem is that you did not understand the problem of the OP at all and 
pointing blindly to the manual is useless


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Changing a field's data in every record

[Lucio Chiappetti]

On Sat, 18 Feb 2017, debt wrote:


How does one "grab" the existing data and then change it? Can this
be done solely in MySQL


I am not sure to understand your question ... you usually manipulate data 
inside mysql ... but here it seems to me you are not talking of changing 
the data VALUE (UPDATE table SET column=new value WHERE ...) but of 
changing the DATA TYPE (ALTER TABLE table CHANGE or ALTER TABLE table 
MODIFY depending on whether ypou change the column name too) or even of 
(just) changing the FORMAT in which a value is displayed ...



Is there a formula to change the format of the data in a single
field in every record of a table?  She has a "timestamp??? in a
text field formatted as 2017|02|16|04|58|42 and she wants to
convert it to a more human readable format like 2017-02-16 @
04:58:42


curious ... the default format for a time stamp is very similar to the 
latter (except for you funny at-sign) :


mysql> select time from north33 limit 1;
+-+
| time|
+-+
| 2013-01-22 12:47:47 |
+-+

and only if I cast it to an integer I get the other form

mysql> select time+0 from north33 limit 1;
++
| time+0 |
++
| 20130122124747 |
++

Anyhow I advise you to read the mysql manual and in particular the 
function chapter and the "time and date function" subchapter. E.g. here


https://dev.mysql.com/doc/refman/5.7/en/functions.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Changing a field's data in every record

[debt]

> On Feb 19, 2017, at 3:50 AM, Brad Barnett  wrote:
> 
> SUBSTRING_INDEX

Thanks a million, Brad!  That works perfectly.  SUBSTRING_INDEX didn’t 
come up in my searches so this is the first I’ve heard of it.

Also, as you could see, it wasn’t a true timestamp.  It is only used 
for viewing, not for searching or sorting, so a text field worked perfectly 
fine for that purpose.  However, after reading everyone’s replies, I convinced 
her to change that field to DATETIME and to lose the ‘@‘.  Now they have the 
best of both worlds - more readable data and a true DATETIME field that will be 
more useful should they ever need it to be.

Thanks again,
Marc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Changing a field's data in every record

[Brad Barnett]

Erm.

I've seen some weird responses to this.  Yes, you can do this.

First -- get the data into a usable format.  Then, put it into a usable
format (eg, timestamp for datetime field).

Read up on how mysql interprets date/time data on fields.  And, create a
new timestamp or date field.

Then, do something like this:

update table set timestamp_field=concat(
SUBSTRING_INDEX(bah,'|',1),"/",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-5),'|',1),"/",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-4),'|',1)," ",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-3),'|',1),":",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-2),'|',1),":",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-1),'|',1));

All the data will then be in that timestamp field or datatime column.  A
datetime column is very readable.

As others have mentioned (nicely, and not so nicely), you can easily
format the output of a timestamp or datetime as wanted.






 On Sat, 18 Feb 2017 13:13:38 -0800
debt  wrote:

>   I’ve been asked to post a question here for a friend.
> 
>   Is there a formula to change the format of the data in a single
> field in every record of a table?  She has a "timestamp” in a text
> field formatted as 2017|02|16|04|58|42 and she wants to convert it to a
> more human readable format like  2017-02-16 @ 04:58:42
> 
>   How does one "grab" the existing data and then change it?  Can
> this be done solely in MySQL, or will she have to grab the data and
> then manipulate it in PHP or something?
> 
> Thanks,
> Marc
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Changing a field's data in every record

[Reindl Harald]

Am 19.02.2017 um 11:11 schrieb Peter Brawley:

On 2/18/2017 15:13, debt wrote:

I’ve been asked to post a question here for a friend.

Is there a formula to change the format of the data in a single
field in every record of a table?  She has a "timestamp” in a text
field formatted as 2017|02|16|04|58|42 and she wants to convert it to
a more human readable format like  2017-02-16 @ 04:58:42

How does one "grab" the existing data and then change it?


If it's a timestamp, it's saved as 2017-02-16 04:58:42, not as you
showed it, and there's no need to change it, indeed she couldn't.
Instead, in a query that retrieves the timestamp, use the mysql
date_format() function to format the timestamp as desired.


you missed the quotes around timestamp as well as "in a text field 
formatted" and so THERE IS a need to change it - please consider to read 
before answer


as i read the post i hoped someone will say it nicer in between: 
*nobody* right in his mind saves timestamps in a "human readable format" 
but as a unix timestamp or date with the correct field type


unix timestamps have the advantage that they can be also in PHP simple 
compared with <>, < and > because it#s just the seconds since 1970-01-0-1


since this is nothing but random text you just need to select the data, 
split them, chnage the filed type, write them back and replace the 
direct output with strftime()


http://php.net/manual/de/function.explode.php
http://php.net/manual/de/function.mktime.php
http://php.net/manual/de/function.strftime.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Changing a field's data in every record

[Peter Brawley]

On 2/18/2017 15:13, debt wrote:

I’ve been asked to post a question here for a friend.

Is there a formula to change the format of the data in a single field in 
every record of a table?  She has a "timestamp” in a text field formatted as 
2017|02|16|04|58|42 and she wants to convert it to a more human readable format like 
 2017-02-16 @ 04:58:42

How does one "grab" the existing data and then change it?


If it's a timestamp, it's saved as 2017-02-16 04:58:42, not as you 
showed it, and there's no need to change it, indeed she couldn't. 
Instead, in a query that retrieves the timestamp, use the mysql 
date_format() function to format the timestamp as desired.


PB


   Can this be done solely in MySQL, or will she have to grab the data and then 
manipulate it in PHP or something?

Thanks,
Marc



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql