I'm trying to create a summary log by hour. Here's the query (somewhat
simplified):
select to_char(mtrantime,'mm-dd hh AM') as datetime,
count(*) as tot from memtran
group by datetime
order by datetime;
The problem is this produces the data in the following order:
datetime | tot
-------------+-----
04-08 01 PM | 14
04-08 02 PM | 15
04-08 03 PM | 23
04-08 07 AM | 8
04-08 08 AM | 54
04-08 09 AM | 30
04-08 10 AM | 11
04-08 11 AM | 10
04-08 11 PM | 7
04-08 12 PM | 10
What I'd really like is to get it in chronological order by hour:
04-08 07 AM | 8
04-08 08 AM | 54
04-08 09 AM | 30
04-08 10 AM | 11
04-08 11 AM | 10
04-08 12 PM | 10
04-08 01 PM | 14
04-08 02 PM | 15
04-08 03 PM | 23
04-08 11 PM | 7
I would prefer not to show the time of day in 24 hour format, but
there doesn't appear to be a way to order by something that
isn't in the select and group by clause and I don't want to display
the hour twice.
Putting the AM/PM before the HH (which looks a bit clumsy) almost works,
except that 12PM gets sorted to the bottom after 11PM.
Is there an easy way around this?
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org 4 7587
How about :
select to_char(mtrantime,'mm-dd hh AM') as datetime,
to_char(mtrantime,'AM') as sort_field,
count(*) as tot from memtran
group by sort_field, datetime
order by sort_field, datetime;
Then ignore the sort_field column?
Michael
"Mike Nolan" <no***@gw.tssi.com> wrote in message
news:20*************************@gw.tssi.com... I'm trying to create a summary log by hour. Here's the query (somewhat simplified):
select to_char(mtrantime,'mm-dd hh AM') as datetime, count(*) as tot from memtran group by datetime order by datetime;
The problem is this produces the data in the following order:
datetime | tot -------------+----- 04-08 01 PM | 14 04-08 02 PM | 15 04-08 03 PM | 23 04-08 07 AM | 8 04-08 08 AM | 54 04-08 09 AM | 30 04-08 10 AM | 11 04-08 11 AM | 10 04-08 11 PM | 7 04-08 12 PM | 10
What I'd really like is to get it in chronological order by hour:
04-08 07 AM | 8 04-08 08 AM | 54 04-08 09 AM | 30 04-08 10 AM | 11 04-08 11 AM | 10 04-08 12 PM | 10 04-08 01 PM | 14 04-08 02 PM | 15 04-08 03 PM | 23 04-08 11 PM | 7
I would prefer not to show the time of day in 24 hour format, but there doesn't appear to be a way to order by something that isn't in the select and group by clause and I don't want to display the hour twice.
Putting the AM/PM before the HH (which looks a bit clumsy) almost works, except that 12PM gets sorted to the bottom after 11PM.
Is there an easy way around this? -- Mike Nolan
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
Mike Nolan <no***@gw.tssi.com> writes: select to_char(mtrantime,'mm-dd hh AM') as datetime, count(*) as tot from memtran group by datetime order by datetime; The problem is this produces the data in the following order: ... What I'd really like is to get it in chronological order by hour:
You are grouping/ordering by the textual result of to_char(),
in which PM naturally follows AM. I think the behavior you
want would come from grouping/ordering by the underlying
timestamp column "mtrantime".
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
> You are grouping/ordering by the textual result of to_char(), in which PM naturally follows AM. I think the behavior you want would come from grouping/ordering by the underlying timestamp column "mtrantime".
Well, I need it grouped by hour, but that led me to the solution:
select to_char(date_trunc('hour',mtrantime),'mm-dd hh AM') as
datetime, count(*) as tot, from memtran
group by mtranoper, date_trunc('hour',mtrantime)
order by mtranoper, date_trunc('hour',mtrantime)
I knew there had to be a straight-forward solution. Thanks Tom.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
> How about : select to_char(mtrantime,'mm-dd hh AM') as datetime, to_char(mtrantime,'AM') as sort_field, count(*) as tot from memtran group by sort_field, datetime order by sort_field, datetime;
Then ignore the sort_field column?
I usually don't like to send managers reports with data labeled
'ignore this column'. :-)
With Tom's help, I found a solution.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bryan Harrington |
last post by:
Can you use Select top 40 * from ... and ORDER BY... in the same query?
I have the following 4 quearies that are.. for the most part, the same
except the order by clause, all return a differnt...
|
by: brett |
last post by:
Here is my SQL string:
"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON...
|
by: war_wheelan |
last post by:
I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID,...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: Jean |
last post by:
Hello all,
I have the following data, that was queried and sorted to columns
PROBLEM_ID and then by STATUSDATE (ascending):
STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ...
|
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) *...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Simon Gare |
last post by:
Hi,
trying to retrieve postal codes from the db but only want the query to look
at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I...
|
by: djflow |
last post by:
Hi!
II was wondering if you can help me with SQL query..
Below 7 separated select query works fine(only when they are retrieved separately)
But I want to combined them together and so that i...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |