Обсуждение: Alternative to AS?
Hi! I am moving a database project from MySQL to PostgreSQL I was a newbie there and now I am a newbie here :) I have this form mysql: SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar; Which I have gotten postgre to accept thus far: SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; it stops at AS which doesn't seem to exist is postgre, what is postgres alternative to AS? Thank's in advance, Helgi Örn
Hi, From You've written You want to convert time formatting. If that is the case thisarticle may be of help: http://www.postgresql.org/docs/8.0/static/datatype-datetime.html 2010/10/6 Helgi Örn <sacredeagle@gmail.com>: > Hi! I am moving a database project from MySQL to PostgreSQL I was a > newbie there and now I am a newbie here :) > > I have this form mysql: > SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar; > > Which I have gotten postgre to accept thus far: > SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; > > it stops at AS which doesn't seem to exist is postgre, what is > postgres alternative to AS? > > > Thank's in advance, > Helgi Örn > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > -- Łukasz Brodziak II MU Bioinformatyka
Helgi Örn, 06.10.2010 10:58: > Hi! I am moving a database project from MySQL to PostgreSQL I was a > newbie there and now I am a newbie here :) > > I have this form mysql: > SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar; > > Which I have gotten postgre to accept thus far: > SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; > > it stops at AS which doesn't seem to exist is postgre, what is > postgres alternative to AS? You have an extra comma in front of the AS, which is causing the error. But even then, I don't think your formatting would work, you will need to use the to_char() function for that. Regards Thomas P.S.: it's "postgres" not "postgre" ;)
Well, I'm sorry if I wasn't clear enough but this is not what I was looking for. 2010/10/6 Lukasz Brodziak <lukasz.brodziak@gmail.com>: > Hi, > > From You've written You want to convert time formatting. If that is > the case thisarticle may be of help: > http://www.postgresql.org/docs/8.0/static/datatype-datetime.html > I've read this MANY times when I needed to create a TIME column and I have to say; it's not written for newbies :) What I actually need to know is the postgre (or SQL) alternative to AS used in mysql. My query (below) is accepted by postgre until AS where it stops (^) ERROR: syntax error at or near "AS" Thank's for your effort, /HÖ > 2010/10/6 Helgi Örn <sacredeagle@gmail.com>: >> Hi! I am moving a database project from MySQL to PostgreSQL I was a >> newbie there and now I am a newbie here :) >> >> I have this form mysql: >> SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar; >> >> Which I have gotten postgre to accept thus far: >> SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; >> >> it stops at AS which doesn't seem to exist is postgre, what is >> postgres alternative to AS? >> >> >> Thank's in advance, >> Helgi Örn >> >> -- >> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice >> > > > > -- > Łukasz Brodziak > II MU Bioinformatyka >
>>>
>>> I have this form mysql:
>>> SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar;
I think you want something like:
PG=# select '23:59' as timestring, '23:59'::time as format ;
timestring | format
------------+----------
23:59 | 23:59:00
(1 row)
Or as you seem to have "." as separator (tough it depends probably on
the locale if 23.59 is a time or not)
PG=# select '23.59' as timestring, replace('23.59', '.',':')::time as
format;
timestring | format
------------+----------
23.59 | 23:59:00
Adapted to your example:
SELECT tid_in, replace(tid_in, '.', ':')::time as format FROM timmar;
All in all, "as" should work as you think, it's more the time conversion
/ from-string-parsing that's different.
Christian
Thank's for your effort Nathaniel. 2010/10/6 Nathaniel Trellice <naptrel@yahoo.co.uk>: > Hi Helgi, > > I think your problem is not the 'AS', but is the way you're trying to format the time. > What I'm trying to do is fetch time from the database and transform it into a 'human readable' format. > I don't know what 'tid_in' is, but you might have some luck trying something along the lines of: > tid_in is the name of the column that is type TIME > SELECT tid_in, to_char(tid_in, 'HH24.MI') AS format FROM timmar; > This does not work at all. > The section 'Data Type Formatting Functions' (chapter 9.8) may be of a little help. > Believe me, I have read through this but in this case it hasn't helped me the least. What I miss in the postgres documentation i samples > Nathaniel > > > On 6 Oct 2010, at 09:58, Helgi Örn <sacredeagle@gmail.com> wrote: > >> Hi! I am moving a database project from MySQL to PostgreSQL I was a >> newbie there and now I am a newbie here :) >> >> I have this form mysql: >> SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar; >> >> Which I have gotten postgre to accept thus far: >> SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; >> >> it stops at AS which doesn't seem to exist is postgre, what is >> postgres alternative to AS? >> >> >> Thank's in advance, >> Helgi Örn >>
Helgi Örn, 06.10.2010 20:36: >> SELECT tid_in, to_char(tid_in, 'HH24.MI') AS format FROM timmar; >> > This does not work at all. "Does not work" is not a valid Postgres error message. What exactly is "not working"? postgres=> create table timmar ( tid_in time ); CREATE TABLE postgres=> insert into timmar values (TIME '23:24'); INSERT 0 1 postgres=> commit; COMMIT postgres=> SELECT to_char(tid_in, 'HH24.MI') AS format FROM timmar; format -------- 23.24 (1 row) If this is not what you want, you need to be more detailed. Thomas
On 6 October 2010 21:07, Thomas Kellerer <spam_eater@gmx.net> wrote: > Helgi Örn, 06.10.2010 20:36: >>> >>> SELECT tid_in, to_char(tid_in, 'HH24.MI') AS format FROM timmar; >>> >> This does not work at all. > > "Does not work" is not a valid Postgres error message. > What exactly is "not working"? > It didn't work and the outcome was none at all. Before, the prompt had changed from ekobas=# to ekobas'#? > postgres=> create table timmar ( tid_in time ); > CREATE TABLE > postgres=> insert into timmar values (TIME '23:24'); > INSERT 0 1 > postgres=> commit; > COMMIT > postgres=> SELECT to_char(tid_in, 'HH24.MI') AS format FROM timmar; > format > -------- > 23.24 > (1 row) > > If this is not what you want, you need to be more detailed. > > Thomas > I AM SO VERY SORRY TO ALL OF YOU GUYS! I made a dreadful mistake when I created this tabel I call "timmar" (hours, that's working hours), it includes three TIME columns which I created in phpPgAdmin which shows very small letters on my computer. Two of these columns started with capital P instead of p. Terrible mistake, me bad :( This works very well: SELECT tid_in, to_char(tid_in, 'HH24.MI') AS format FROM timmar; Thank you all for your effort. /HÖ
Helgi Örn Helgason, 06.10.2010 21:43: > It didn't work and the outcome was none at all. Before, the prompt had > changed from ekobas=# to ekobas'#? You did not terminate your statement with a semicolon, so psql was "waiting" for you to complete the statement rather thanexecuting it. Regards Thomas
sacredeagle@gmail.com (Helgi Örn) writes: > Hi! I am moving a database project from MySQL to PostgreSQL I was a > newbie there and now I am a newbie here :) > > I have this form mysql: > SELECT tid_in, TIME_FORMAT(tid_in, '%H.%i')AS format FROM timmar; > > Which I have gotten postgre to accept thus far: > SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; > > it stops at AS which doesn't seem to exist is postgre, what is > postgres alternative to AS? I see two problems here... 1. You need to use Postgres idioms for the reformatting of the date. <http://www.postgresql.org/docs/9.0/static/functions-formatting.html> 2. You put in a syntax error when you put in an extra comma. Instead of: > SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; ^ Take out the comma to get: SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i' AS format FROM timmar; Possibly a more suitable query would be: select pack_tidin, to_char (pack_tidin, 'HH.MI') as format from timmar; I'm assuming that '%H.%i' is intended to return hours and minutes, separated by a ".". You can probably get more useful guidance from the URL listed above. -- (format nil "~S@~S" "cbbrowne" "acm.org") http://linuxfinances.info/info/postgresql.html We're Thinking Machines, so we don't have to.
On 6 October 2010 21:56, Thomas Kellerer <spam_eater@gmx.net> wrote: > Helgi Örn Helgason, 06.10.2010 21:43: >> >> It didn't work and the outcome was none at all. Before, the prompt had >> changed from ekobas=# to ekobas'#? > > You did not terminate your statement with a semicolon, so psql was "waiting" > for you to complete the statement rather than executing it. > > Regards > Thomas > But when that happens the prompt changes to ekobas-# right? /HÖ
> 1. You need to use Postgres idioms for the reformatting of the date. > > <http://www.postgresql.org/docs/9.0/static/functions-formatting.html> > > 2. You put in a syntax error when you put in an extra comma. > > Instead of: >> SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i', AS format FROM timmar; > ^ > Take out the comma to get: > SELECT pack_tidin TIME_FORMAT, pack_tidin '%H.%i' AS format FROM timmar; > > Possibly a more suitable query would be: > > select pack_tidin, to_char (pack_tidin, 'HH.MI') as format from timmar; > This works fine, the only problem is the outcome the two columns: pack_tidin | format ------------+-------- 07:45:00 | 07.45 08:00:00 | 08.00 07:00:00 | 07.00 06:30:00 | 06.30 This is precisely what I wanted: ekobas=# SELECT to_char(pack_tidin, 'HH24.MI') AS format FROM timmar; format -------- 07.45 08.00 07.00 06.30 > I'm assuming that '%H.%i' is intended to return hours and minutes, > separated by a ".". You can probably get more useful guidance from the > URL listed above. > Yes, the time format in the database is hh:mm:ss which I do not want om my page, I want this hh.mm which is quite common in at least in europe. Thank's, /HÖ
Helgi Örn Helgason, 06.10.2010 22:05: > On 6 October 2010 21:56, Thomas Kellerer<spam_eater@gmx.net> wrote: >> Helgi Örn Helgason, 06.10.2010 21:43: >>> >>> It didn't work and the outcome was none at all. Before, the prompt had >>> changed from ekobas=# to ekobas'#? >> >> You did not terminate your statement with a semicolon, so psql was "waiting" >> for you to complete the statement rather than executing it. >> > But when that happens the prompt changes to ekobas-# right? Correct
Hi On 6 October 2010 21:56, Thomas Kellerer <spam_eater@gmx.net> wrote: > Helgi Örn Helgason, 06.10.2010 21:43: >> >> It didn't work and the outcome was none at all. Before, the prompt had >> changed from ekobas=# to ekobas'#? > > You did not terminate your statement with a semicolon, so psql was "waiting" > for you to complete the statement rather than executing it. I believe he did not terminate a string with a single quote character (or he forgot the one at the start of the string). -- Michael Wood <esiotrot@gmail.com>